current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.6 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 952 down vote favorite
201
 
SELECT GETDATE()

Returns: 2008-09-22 15:24:13.790

I want that date part without the time part: 2008-09-22 00:00:00.000

share|improve this question
1  
If you are looking to get a date datatype without the time, even if the time is 00:00:00 then you are out of luck, you can get a varchar but the structure is a datetime and you will always have some time. – Quintin Robinson Sep 22 '08 at 3:39
9  
One thing to note is that SQL Server 2008 includes a separate DATE datatype for storing just dates without the time component. More info here: sql-server-performance.com/articles/dev/datetime_2008_p1.asp‌​x – Ben Hoffstein Sep 22 '08 at 3:44
5  
Don't miss this post showing performance testing results of the various time-removal methods. – ErikE Aug 17 '12 at 22:02
12  
Don't be mislead by the votes and accepted answer, Take a look at stackoverflow.com/a/126984/1155650 – Rohit Nov 26 '13 at 10:24
3  
@Rohit You are incorrectly assuming that 2008 is the only version people care about. (There are more versions in the wild.) The votes speak for themselves. – hktegner Dec 14 '13 at 18:27

33 Answers 33

1 2
up vote 1493 down vote accepted

On SQL Server 2008 and higher, you should convert to date:

SELECT CONVERT(date, getdate())

On older versions, you can do the following:

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))

for example

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

gives me 

2008-09-22 00:00:00.000

Pros:

  • No varchar<->datetime conversions required
  • No need to think about locale
share|improve this answer
26  
+1 Looks like this one is 35% faster than the double convert() method commonly used (which I also have used for years). Nice one. – Dane Sep 22 '08 at 4:04
5  
The only downside I can see to your solution is that unless you know what it is doing it is a bit obtuse. Using the double convert method makes your intentions more obvious to futire code maintainers. BTW I have not downvoted you. I think I'll start using your method too. Thankyou @aku – Jim Birchall Sep 24 '08 at 8:25
30  
@pilavdzice Setting a datetime to midnight of that day does LEAVE OFF THE TIME. What result are you expecting? The datetime data type cannot have no time at all. I think you are confusing data storage with user presentation. If all you want is a way to show a user a string that has no time portion (not zeroes, just blanks) then you simply want Convert(varchar(30), @Date, 101) or something similar. See SQL Server Books Online • Cast and Convert for more info. – ErikE Aug 17 '12 at 22:03
3  
@user1671639 the datetime data type always contains both a date and a time, you can't sensibly store one without the other - unless you're using SQL Server 2008, in which case there are also separate 'date' and 'time' data types. If you use CONVERT() like that, you really want a string for later use, so you'll be stuck doing it like that - although it'd be better if you used date formatting functions instead of cutting the date off - or via CAST(... AS DATE) or CONVERT(DATE, ...), which has been mentioned quite often on this very page. – Magnus Jun 21 '13 at 15:08
5  
I recommend changing the answer to SELECT DATEADD(dd, DATEDIFF(dd, 0, @your_date), 0) because then dd can be swapped out for any other datepart keyword to truncate your datetime at an arbitrary level. – Michael Aug 14 '14 at 16:08
up vote 568 down vote
+500

SQLServer 2008 now has a 'date' data type which contains only a date with no time component. Anyone using SQLServer 2008 and beyond can do the following:

SELECT CONVERT(date, GETDATE())
share|improve this answer
25  
There is also the 'time' data type in SQL2008 which answers the other half of the question of separating date and time. – misteraidan Aug 25 '11 at 0:01
3  
FYI, I benchmarked different methods of trimming off time from dates and this was the fastest method. Granted the difference was small, but it was clearly faster over a large # of executions. – Ghost Jul 3 '14 at 12:48
    
wt about sqlserver 2005?? – Dr. MAF Nov 19 '15 at 9:10
up vote 102 down vote

If using SQL 2008 and above:

select cast(getdate() as date)
share|improve this answer
1  
@FredrickGauss: What type, Date? What version of SQL Server do you use? – abatishchev Dec 13 '12 at 20:01
11  
Great, but only works in SQL 2008 – mike nelson Apr 10 '13 at 23:09
3  
Beware! declare @date1 datetime = '2015-09-30 20:59:59.999'; select cast(@date1 as date) returns '2015-10-01' – Nick Sep 24 '15 at 19:18
2  
@Nick: this is the issue with DateTime. use DateTime2 instead and it works fine. sqlfiddle.com/#!6/9eecb7/2833 – abatishchev Sep 25 '15 at 1:33
3  
@Nick, to complement abatishchev response, your @date1 is indeed 2015-10-01, due to DateTime limitations. Try without any cast to Date, it yields 2015-10-01too! declare @date1 datetime = '2015-09-30 23:59:59.999';select @date1 => 2015-10-01 – Frédéric Dec 11 '15 at 17:07
up vote 58 down vote

DATEADD and DATEDIFF are better than CONVERTing to varchar. Both queries have the same execution plan, but execution plans are primarly about data access strategies and do not always reveal implicit costs involved in the CPU time taken to perform all the pieces. If both queries are run against a table with millions of rows, the CPU time using DateDiff can be close to 1/3rd of the Convert CPU time!

To see execution plans for queries:

set showplan_text on
GO

Both DATEADD and DATEDIFF will execute a CONVERT_IMPLICIT.

Although the CONVERT solution is simpler and easier to read for some, it is slower. There is no need to cast back to datetime (this is implicitly done by the server). There is also no real need in the DateDiff method for DateAdd afterward as the integer result will also be implicitly converted back to datetime.

SELECT CONVERT(varchar, MyDate, 101) FROM DatesTable

  |--Compute Scalar(DEFINE:([Expr1004]=CONVERT(varchar(30),[TEST].[dbo].[DatesTable].[MyDate],101)))
       |--Table Scan(OBJECT:([TEST].[dbo].[DatesTable]))

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, MyDate)) FROM DatesTable

  |--Compute Scalar(DEFINE:([Expr1004]=dateadd(day,(0),CONVERT_IMPLICIT(datetime,datediff(day,'1900-01-01 00:00:00.000',CONVERT_IMPLICIT(datetime,[TEST].[dbo].[DatesTable].[MyDate],0)),0))))
       |--Table Scan(OBJECT:([TEST].[dbo].[DatesTable]))

Using FLOOR() as @digi suggested has performance closer to DateDiff, but is not recommended as casting the datetime data type to float and back does not always yield the original value.

Remember guys: Don't believe anyone. Look at the performance statistics, and test it yourself!

Be careful when you're testing your results. Selecting many rows to the client will hide the performance difference becauses it takes longer to send the rows over the network than it does to perform the calculations. So make sure that the work for all the rows is done by the server but there is no rowset sent to the client.

There seems to be confusion for some people about when cache optimization affects queries. Running two queries in the same batch or in separate batches has no effect on caching. So you can either expire the cache manually or simply run the queries back and forth multiple times. Any optimization for query #2 would also affect any subsequent queries, so throw out execution #1 if you like.

Here is full test script and performance results that prove DateDiff is substantially faster than converting to varchar.

share|improve this answer
    
Ricardo C, nice investigation! What version of SQL server do you use? On MSSQL2000 method with datediff performs slightly faster for me. – aku Sep 24 '08 at 5:29
    
Just to note, I performed test 1000.000 times. For real-world scenarios performance difference will not be noticeable, I guess – aku Sep 24 '08 at 5:30
    
Aku, I used SQL Server 2005 Express for this test. I work on 2000 at work, and I will test it with a table with over 24 million rows and see what comes out of it. – Ricardo C Sep 24 '08 at 6:20
    
Ricardo C, cool! I'm really interested how it will perform on real data base. – aku Sep 24 '08 at 7:51
3  
The claims about equal performance are not true. Of course the execution plans will be the same!!! Measuring performance on these MUST be done by comparing CPU usage, not examining execution plans. – ErikE Sep 12 '10 at 23:01
up vote 30 down vote 
SELECT CONVERT(VARCHAR(10),GETDATE(),111)
share|improve this answer
2  
This returns '2008/09/22' for me – eddiegroves Sep 22 '08 at 3:41
    
111 is the Japanese format. yyy/mm/dd – Ricardo C Sep 24 '08 at 5:00
    
SELECT CONVERT(VARCHAR(10),GETDATE(),101) is mm/dd/yyyy format. – Flea Jul 10 '13 at 20:47
2  
if you're sorting based on the raw text value (outside of the DB) then the 'japanese' format is better – Simon_Weaver Sep 14 '13 at 0:34
    
It works on SQL Server 2005. – Raphael Amoedo Aug 19 '15 at 17:41
up vote 25 down vote 
SELECT CONVERT(datetime, CONVERT(varchar, GETDATE(), 101))
share|improve this answer
up vote 15 down vote

For return in date format

CAST(OrderDate AS date)

The above code will work in sql server 2010

It will return like 12/12/2013

For SQL Server 2012 use the below code

CONVERT(VARCHAR(10), OrderDate , 111)
share|improve this answer
    
This returns me date with zero time, not just date – Bohemian♦ Jan 5 '14 at 13:09
    
can i know which version if sql server you are using? – Mahesh ML Mar 19 '14 at 7:15
    
@MaheshML it returns both date and time in MS SQL 2012. – Marek Apr 13 '14 at 15:50
    
@Marek It is working fine with MS SQL 2008 – Mahesh ML May 2 '14 at 11:19
    
Works like a charm in SQL Azure – Martín Coll May 21 '14 at 15:01
up vote 14 down vote

You can use the CONVERT function to return only the date. See the link(s) below:

Date and Time Manipulation in SQL Server 2000

CAST and CONVERT

The syntax for using the convert function is: 

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
share|improve this answer
up vote 9 down vote

Using FLOOR() - just cut time part.

SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)
share|improve this answer
1  
This method is not the fastest, and also implicitly teaches people that casting dates to float is accurate, which it is not. Please see this post for more detail. – ErikE Sep 12 '10 at 23:16
4  
Float Quantization errors are scary – messenger Dec 6 '10 at 22:09
up vote 9 down vote

IF you want to use CONVERT and get the same output as in the original question posed, that is, yyyy-mm-dd then use CONVERT(varchar(10),[SourceDate as dateTime],121) same code as the previous couple answers, but the code to convert to yyyy-mm-dd with dashes is 121.

If I can get on my soapbox for a second, this kind of formatting doesn't belong in the data tier, and that's why it wasn't possible without silly high-overhead 'tricks' until SQL Server 2008 when actual datepart data types are introduced. Making such conversions in the data tier is a huge waste of overhead on your DBMS, but more importantly, the second you do something like this, you have basically created in-memory orphaned data that I assume you will then return to a program. You can't put it back in to another 3NF+ column or compare it to anything typed without reverting, so all you've done is introduced points of failure and removed relational reference.

You should ALWAYS go ahead and return your dateTime data type to the calling program and in the PRESENTATION tier, make whatever adjustments are necessary. As soon as you go converting things before returning them to the caller, you are removing all hope of referential integrity from the application. This would prevent an UPDATE or DELETE operation, again, unless you do some sort of manual reversion, which again is exposing your data to human/code/gremlin error when there is no need.

share|improve this answer
    
Except, say, if you want a query that retrieves all records matching a user-supplied date as the date-part of a certain time field. Good luck doing that only in the presentation layer. (You don't need convert, you can can use date arithmetic, but you get the idea…) – Andrew Lazarus Mar 14 '13 at 16:42
    
@Andrew why does that matter? You say WHERE col >= @Date AND col < DATEADD(DAY, 1, @Date); - there is absolutely no reason to strip time from the column. – Aaron Bertrand Nov 16 '13 at 5:09
    
@AaronBertrand That only works assuming the input @Date has a zero time part. In case that isn't true, you still need to know how to truncate times server-side. I agree with this answer that formatting should be left to the presentation layer, but I didn't agree with an implication that leaving that for the front end means you don't have to know a quick way to truncate. – Andrew Lazarus Nov 16 '13 at 17:04
    
@Andrew all you have to do is make the input parameter DATE. My point is still that you should never have to apply any such truncation to the column, even though that is most people's first instinct. – Aaron Bertrand Nov 16 '13 at 20:25
    
@AaronBertrand and that assumes you have control over the datatype of the parameter. Fine in a stored procedure, not so possible in other situations. Why not cast to be sure the parameter is the type you want and need? – Andrew Lazarus Nov 18 '13 at 23:42
up vote 8 down vote 
SELECT DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0)

SELECT DATEADD(DAY, 0, DATEDIFF(DAY,0, GETDATE()))

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 101))

Edit: The first two methods are essentially the same, and out perform the convert to varchar method.

share|improve this answer
    
These methods are all great, but which single one do you suggest using? – eddiegroves Sep 22 '08 at 3:48
1  
Note that the "correct" version of the top two is select dateadd(dd, datediff(dd, 0, getdate()), 0), because the dds can then be swapped out for any of the datepart keywords to clip the date at any segment you choose. (Also note that dd is just an abbreviation for day.) – Michael Aug 14 '14 at 16:02
up vote 6 down vote 
 Convert(nvarchar(10), getdate(), 101) --->  5/12/14

 Convert(nvarchar(12), getdate(), 101) --->  5/12/2014
share|improve this answer
up vote 5 down vote

If you need result in varchar datatype you should go through

SELECT CONVERT(DATE, GETDATE()) --2014-03-26
SELECT CONVERT(VARCHAR(10), GETDATE(), 111) --2014/03/26

which is already mentioned above

If you need result in date and time format you should go through any of the below query

1) SELECT CONVERT(DATETIME,CONVERT(VARCHAR(10), GETDATE(), 111)) as OnlyDate --2014-03-26 00:00:00.000

2) SELECT CONVERT(DATETIME,CONVERT(VARCHAR(10), GETDATE(), 112)) as OnlyDate --2014-03-26 00:00:00.000

3)

 DECLARE  @OnlyDate DATETIME
   SET @OnlyDate = DATEDIFF(DD, 0, GETDATE())
   SELECT @OnlyDate AS OnlyDate

--2014-03-26 00:00:00.000

share|improve this answer
up vote 4 down vote

I think this would work in your case:

CONVERT(VARCHAR(10),Person.DateOfBirth,111) AS BirthDate
//here date is obtained as 1990/09/25
share|improve this answer
up vote 4 down vote

Even using the ancient MSSQL Server 7.0, the code here (courtesy of this link) allowed me to get whatever date format I was looking for at the time: 

PRINT '1) Date/time in format MON DD YYYY HH:MI AM (OR PM): ' + CONVERT(CHAR(19),GETDATE())  
PRINT '2) Date/time in format MM-DD-YY: ' + CONVERT(CHAR(8),GETDATE(),10)  
PRINT '3) Date/time in format MM-DD-YYYY: ' + CONVERT(CHAR(10),GETDATE(),110) 
PRINT '4) Date/time in format DD MON YYYY: ' + CONVERT(CHAR(11),GETDATE(),106)
PRINT '5) Date/time in format DD MON YY: ' + CONVERT(CHAR(9),GETDATE(),6) 
PRINT '6) Date/time in format DD MON YYYY HH:MM:SS:MMM(24H): ' + CONVERT(CHAR(24),GETDATE(),113)

It produced this output: 

1) Date/time in format MON DD YYYY HH:MI AM (OR PM): Feb 27 2015  1:14PM
2) Date/time in format MM-DD-YY: 02-27-15
3) Date/time in format MM-DD-YYYY: 02-27-2015
4) Date/time in format DD MON YYYY: 27 Feb 2015
5) Date/time in format DD MON YY: 27 Feb 15
6) Date/time in format DD MON YYYY HH:MM:SS:MMM(24H): 27 Feb 2015 13:14:46:630
share|improve this answer
up vote 4 down vote

To obtain the result indicated, I use the following command.

SELECT CONVERT(DATETIME,CONVERT(DATE,GETDATE()))

I holpe it is useful.

share|improve this answer
up vote 4 down vote

Okay, Though I'm bit late :), Here is the another solution.

SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) as DATETIME)

Result

2008-09-22 00:00:00.000

And if you are using SQL Server 2012 and higher then you can use FORMAT() function like this -

SELECT FORMAT(GETDATE(), 'yyyy-MM-dd')
share|improve this answer
    
Your first example still has a time component. The point of the question was how to remove that. – Zack Apr 20 '16 at 21:20
up vote 4 down vote 
SELECT CONVERT(VARCHAR,DATEADD(DAY,-1,GETDATE()),103) --21/09/2011

SELECT CONVERT(VARCHAR,DATEADD(DAY,-1,GETDATE()),101) --09/21/2011

SELECT CONVERT(VARCHAR,DATEADD(DAY,-1,GETDATE()),111) --2011/09/21

SELECT CONVERT(VARCHAR,DATEADD(DAY,-1,GETDATE()),107) --Sep 21, 2011
share|improve this answer
    
Thanks this solved the issue and gave me clear examples. – MDM yesterday
up vote 2 down vote

why don't you use DATE_FORMAT( your_datetiem_column, '%d-%m-%Y' ) ?

EX: select DATE_FORMAT( some_datetime_column, '%d-%m-%Y' ) from table_name

you can change sequence of m,d and year by re-arranging '%d-%m-%Y' part

share|improve this answer
up vote 2 down vote 
DECLARE @yourdate DATETIME = '11/1/2014 12:25pm'    
SELECT CONVERT(DATE, @yourdate)
share|improve this answer
    
Please explain what's going on in your answer. – Ben Nov 19 '14 at 21:17
    
This suggestion has been covered by other answers (more than once). – Andriy M Nov 19 '14 at 21:47
up vote 2 down vote

If you are using SQL Server 2012 or above versions,

Use Format() function.

There are already multiple answers and formatting types for SQL server. But most of the methods are somewhat ambiguous and it would be difficult for you to remember the numbers for format type or functions with respect to Specific Date Format. That's why in next versions of SQL server there is better option.

FORMAT ( value, format [, culture ] )

Culture option is very useful, as you can specify date as per your viewers.

You have to remember d (for small patterns) and D (for long patterns).

1."d" - Short date pattern.

2009-06-15T13:45:30 -> 6/15/2009 (en-US)
2009-06-15T13:45:30 -> 15/06/2009 (fr-FR)
2009-06-15T13:45:30 -> 2009/06/15 (ja-JP)

2."D" - Long date pattern.

2009-06-15T13:45:30 -> Monday, June 15, 2009 (en-US)
2009-06-15T13:45:30 -> 15 июня 2009 г. (ru-RU)
2009-06-15T13:45:30 -> Montag, 15. Juni 2009 (de-DE)

More examples in query.

DECLARE @d DATETIME = '10/01/2011';
SELECT FORMAT ( @d, 'd', 'en-US' ) AS 'US English Result'
      ,FORMAT ( @d, 'd', 'en-gb' ) AS 'Great Britain English Result'
      ,FORMAT ( @d, 'd', 'de-de' ) AS 'German Result'
      ,FORMAT ( @d, 'd', 'zh-cn' ) AS 'Simplified Chinese (PRC) Result'; 

SELECT FORMAT ( @d, 'D', 'en-US' ) AS 'US English Result'
      ,FORMAT ( @d, 'D', 'en-gb' ) AS 'Great Britain English Result'
      ,FORMAT ( @d, 'D', 'de-de' ) AS 'German Result'
      ,FORMAT ( @d, 'D', 'zh-cn' ) AS 'Chinese (Simplified PRC) Result';

US English Result Great Britain English Result  German Result Simplified Chinese (PRC) Result
----------------  ----------------------------- ------------- -------------------------------------
10/1/2011         01/10/2011                    01.10.2011    2011/10/1

US English Result            Great Britain English Result  German Result                    Chinese (Simplified PRC) Result
---------------------------- ----------------------------- -----------------------------  ---------------------------------------
Saturday, October 01, 2011   01 October 2011               Samstag, 1. Oktober 2011        2011年10月1日

If you want more formats, you can go to:

  1. Standard Date and Time Format Strings
  2. Custom Date and Time Format Strings
share|improve this answer
up vote 1 down vote

You can use following for date part and formatting the date:

DATENAME => Returns a character string that represents the specified datepart of the specified date

DATEADD => The DATEPART() function is used to return a single part of a date/time, such as year, month, day, hour, minute, etc.

DATEPART =>Returns an integer that represents the specified datepart of the specified date.

CONVERT() = > The CONVERT() function is a general function that converts an expression of one data type to another. The CONVERT() function can be used to display date/time data in different formats.

share|improve this answer
up vote 1 down vote

Date(date&time field) and DATE_FORMAT(date&time,'%Y-%m-%d') both returns only date from date&time

share|improve this answer
    
The question states SQL Server. This seems like MySQL? – The1nk Dec 17 '15 at 18:31
up vote 1 down vote

I favor the following which wasn't mentioned:

DATEFROMPARTS(DATEPART(yyyy, @mydatetime), DATEPART(mm, @mydatetime), DATEPART(dd, @mydatetime))

It also doesn't care about local or do a double convert -- although each 'datepart' probably does math. So it may be a little slower than the datediff method, but to me it is much more clear. Especially when I want to group by just the year and month (set the day to 1).

share|improve this answer
up vote 1 down vote

This was missing in all answers, may not be the most efficient but very easy to write and understand, no style needed, no complex date functions.

SELECT CONVERT(DATETIME,CONVERT(DATE,((GETDATE()))))
share|improve this answer
up vote 1 down vote

You can simply use the code below to get only the date part and avoid the time part in SQL:

SELECT SYSDATE TODAY FROM DUAL;
share|improve this answer
up vote 1 down vote

If you are assigning the results to a column or variable, give it the DATE type, and the conversion is implicit.

DECLARE @Date DATE = GETDATE()
share|improve this answer
up vote 0 down vote

I know this is old, but I do not see where anyone stated it this way. From what I can tell, this is ANSI standard.

SELECT CAST(CURRENT_TIMESTAMP AS DATE)

It would be good if Microsoft could also support the ANSI standard CURRENT_DATE variable.

share|improve this answer
up vote 0 down vote 
SELECT * FROM tablename WHERE CAST ([my_date_time_var] AS DATE)= '8/5/2015'
share|improve this answer
up vote 0 down vote

SELECT CONVERT(date, getdate())

share|improve this answer
1 2

protected by Andriy M Nov 19 '14 at 21:46

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.