how to get today's Date only and not with the time using sql

Question

Possible Duplicate:
Most efficient way in SQL Server to get date from date+time?

I want to update a field with today's date only but the problem i am having now is it is giving me the date and the time. I would like to update with specific format like this: 11/09/2012 how can i achieve this? here is my current code:

UPDATE MyTable  
        SET Field1 = GETDATE()

http://stackoverflow.com/questions/1177449/best-approach-to-remove-time-part-of-datetime-in-sql-server — Mikael Eriksson, Nov 12 '12 at 18:37
FYI, to get it as the format you want, the answer is : `Select Convert( Varchar(10), GetDate(), 101 )`. Beware, the data type is `varchar`. You cannot force the format to MM/DD/YYYY unless it is varchar. — Dominic Goulet, Nov 12 '12 at 18:42

Narsimha · Accepted Answer · 2012-12-04T23:11:12.190

14

One way is to update and select only date (not time)

 UPDATE @tab SET dates=(CONVERT(VARCHAR(10),GETDATE(),103));
 SELECT CONVERT(VARCHAR, GETDATE(), 23) FROM @tab

edited Dec 04 '12 at 23:11

answered Nov 12 '12 at 18:35

Narsimha

184
4

score 4 · Answer 2 · answered Nov 12 '12 at 18:31

Not much different here apart from slight syntax differences however I would recommend wrapping it up in a function.

CREATE FUNCTION [dbo].[GetDateOnly] (@Datetime datetime) 

RETURNS datetime  

AS  

BEGIN  
    RETURN CONVERT(Datetime, FLOOR(CONVERT(float,@Datetime)))  
END

how to get today's Date only and not with the time using sql

2 Answers2