How to return only the Date from a SQL Server DateTime datatype

eddiegroves Source
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

How can I get that?

sqlsql-servertsqldatedatetime

Answers

answered 10 years ago Nescio #1

SELECT CONVERT(VARCHAR(10),GETDATE(),111)

answered 10 years ago Cade Roux #2

SELECT CONVERT(datetime, CONVERT(varchar, GETDATE(), 101))

answered 10 years ago aku #3

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

answered 10 years ago DaveK #4

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 ] ) 

answered 10 years ago Gordon Bell #5

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.

answered 10 years ago DiGi #6

Using FLOOR() - just cut time part.

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

answered 10 years ago Ricardo C #7

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.

answered 10 years ago BenR #8

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())

answered 8 years ago abatishchev #9

If using SQL 2008 and above:

select cast(getdate() as date)

answered 7 years ago Rushda #10

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

answered 6 years ago Focusyn #11

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.

answered 5 years ago Anderson Silva #12

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

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

I holpe it is useful.

answered 5 years ago bishnu karki #13

I think this would work in your case:

CONVERT(VARCHAR(10),Person.DateOfBirth,111) AS BirthDate
//here date is obtained as 1990/09/25

answered 5 years ago Mahesh ML #14

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)

answered 4 years ago Stephon Johns #15

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

answered 4 years ago user1151326 #16

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.

answered 4 years ago Ankit Khetan #17

 Convert(nvarchar(10), getdate(), 101) --->  5/12/14

 Convert(nvarchar(12), getdate(), 101) --->  5/12/2014

answered 4 years ago Janaka R Rajapaksha #18

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

answered 4 years ago etni #19

DECLARE @yourdate DATETIME = '11/1/2014 12:25pm'    
SELECT CONVERT(DATE, @yourdate)

answered 3 years ago Matt O'Brien #20

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

answered 3 years ago Gerard ONeill #21

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).

answered 3 years ago Surekha #22

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

answered 3 years ago Binitta Mary #23

SELECT * FROM tablename WHERE CAST ([my_date_time_var] AS DATE)= '8/5/2015'

answered 3 years ago Imad #24

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()))))

answered 3 years ago lit #25

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.

answered 2 years ago Shyam Bhimani #26

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; 

answered 2 years ago Krishnraj Rana #27

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')

answered 2 years ago Shiraj Momin #28

SELECT CONVERT(date, getdate())

answered 2 years ago Art Schmidt #29

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()   

SELECT @Date   --> 2017-05-03

answered 2 years ago Kris Khairallah #30

Date:

SELECT CONVERT(date, GETDATE())
SELECT CAST(GETDATE() as date)

Time:

SELECT CONVERT(time , GETDATE() , 114)
SELECT CAST(GETDATE() as time)

answered 2 years ago kaub0st3r #31

On SQL Server 2000

CAST(
(
    STR( YEAR( GETDATE() ) ) + '/' +
    STR( MONTH( GETDATE() ) ) + '/' +
    STR( DAY( GETDATE() ) )
)
AS DATETIME)

answered 2 years ago xbb #32

Starting from SQL SERVER 2012, you can do this:

SELECT FORMAT(GETDATE(), 'yyyy-MM-dd 00:00:00.000')

answered 2 years ago Somnath Muluk #33

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

answered 1 year ago Abdul Samad #34

select Cast (getdate() as Date) yourDate

answered 11 months ago Amar Srivastava #35

Simply you can do this way:

SELECT CONVERT(date, getdate())
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

Outputs as:

2008-09-22 00:00:00.000

Or simply do like this:

SELECT CONVERT (DATE, GETDATE()) 'Date Part Only'

Result:

Date Part Only
--------------
2013-07-14

answered 10 months ago Spider #36

My common approach to get date without the time part..

 SELECT CONVERT(VARCHAR(MAX),GETDATE(),103)

 SELECT CAST(GETDATE() AS DATE)

comments powered by Disqus