Welcome to tutel.me
  • Home
  • programming
  • how to return only the date from a sql server datetime datatype

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

By eddiegroves

2008-09-22 03:31:33 8 Comments 

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?

30 comments

@Jithin Joy 2019-05-28 08:34:47

The easiest way would be to use: SELECT DATE(GETDATE())

@Aubrey Love 2019-04-23 17:56:52

Wow, let me count the ways you can do this. (no pun intended)

In order to get the results you want in this format specifically:

2008-09-22

Here are a few options. 

SELECT CAST(GETDATE() AS DATE) AS 'Date1'
SELECT Date2  = CONVERT(DATE, GETDATE())
SELECT CONVERT(DATE, GETDATE()) AS 'Date3'
SELECT CONVERT(CHAR(10), GETDATE(), 121) AS 'Date4'
SELECT CONVERT(CHAR(10), GETDATE(), 126) AS 'Date5'
SELECT CONVERT(CHAR(10), GETDATE(), 127) AS 'Date6'

So, I would suggest picking one you are comfortable with and using that method across the board in all your tables.

All these options return the date in the exact same format. Why does SQL Server have such redundancy?

I have no idea, but they do. Maybe somebody smarter than me can answer that question.

Hope this helps someone.

@aku 2008-09-22 03:34:16

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

As suggested by Michael

Use this variant: SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, getdate()))

select getdate()

SELECT DATEADD(hh, DATEDIFF(hh, 0, getdate()), 0)
SELECT DATEADD(hh, 0, DATEDIFF(hh, 0, getdate()))

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

SELECT DATEADD(mm, DATEDIFF(mm, 0, getdate()), 0)
SELECT DATEADD(mm, 0, DATEDIFF(mm, 0, getdate()))

SELECT DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0)
SELECT DATEADD(yy, 0, DATEDIFF(yy, 0, getdate()))

Output:

2019-04-19 08:09:35.557

2019-04-19 08:00:00.000
4763-02-17 00:00:00.000

2019-04-19 00:00:00.000
2019-04-19 00:00:00.000

2019-04-01 00:00:00.000
1903-12-03 00:00:00.000

2019-01-01 00:00:00.000
1900-04-30 00:00:00.000

@eddiegroves 2008-09-22 03:48:09

Is this way better or worse performance wise than using the convert methods other have suggested? Or is it negligible?

@aku 2008-09-22 03:54:04

My method works faster. It doesn't require conversions to varchar and allows efficient date calculations

@Dane 2008-09-22 04:04:41

+1 Looks like this one is 35% faster than the double convert() method commonly used (which I also have used for years). Nice one.

@Cade Roux 2008-09-22 04:11:16

If this is 35% than the CONVERT method, you've got to wonder how much faster a built-in truncate would be - this has to be the most common datetime-related operation I ever do - I'm going to see about switching to this mechanism.

@Jim Birchall 2008-09-24 08:25:57

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

@ErikE 2010-09-12 23:15:24

+1 You may be interested to see Ricardo C's edited answer (since it is community wiki and factually incorrect, I corrected it). You also got a prop to your question.

@ErikE 2010-09-13 00:51:51

Also don't miss this post showing performance testing results.

@ErikE 2012-08-17 22:03:11

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

@N t 2013-05-20 14:10:40

Worth noting that this does not extend to aggregating monthly, or yearly data. You have to alter the terms in order for that to work. SELECT DATEADD(mm,DATEDIFF(mm, 0, @YourDate),0) is extensible for mm/yy iirc

@Praveen 2013-06-15 11:13:41

@aku Is there a way to get only Datepart as "2008-09-22" and not "2008-09-22 00:00:00.000" without converting it into VARCHAR. Now I'm using CONVERT(VARCHAR(10), @dateTime, 101) AS MyDate

@Magnus 2013-06-21 15:08:48

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

@Michael 2014-08-14 16:08:29

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.

@King Julien 2018-08-19 03:47:09

Dear @aku Please can you help me with this question, stackoverflow.com/questions/51887163/… Thanks!

@karthik kasubha 2019-03-24 17:34:46

select cast(createddate as date) from table where createdate is your datetime column

@Morteza Sefidi 2019-04-09 09:28:58

If using SQL 2008 and above:SELECT CONVERT(DATE ,GETDATE())

@masehhat 2019-04-09 09:38:29

It works for me! Thanks Morteza.

@ankit soni 2019-03-30 11:01:20

you can use like below for different different type of output for date only

  1. SELECT CONVERT(datetime, CONVERT(varchar, GETDATE(), 103)) -----dd/mm/yyyy

  2. SELECT CONVERT(datetime, CONVERT(varchar, GETDATE(), 101))------mm/dd/yyyy

  3. SELECT CONVERT(datetime, CONVERT(varchar, GETDATE(), 102))

@karthik kasubha 2019-03-24 17:36:20 

select cast(createddate as date) as derivedate from table

createdate is your datetime column , this works for sqlserver

@CAGDAS AYDIN 2018-11-21 11:39:50

My Style

      select Convert(smalldatetime,Convert(int,Convert(float,getdate())))

@mokh223 2018-10-05 08:18:37

select convert(getdate() as daselect CONVERT(datetime,CONVERT(date, getdate()))te)

@Rafiqul Islam 2018-10-03 08:21:09

Try This:

SELECT CONVERT(date, GETDATE()) as date

@Harsha Biyani 2018-10-03 09:15:24

Give some explanation so that it would be easy to understand the solution

@Nescio 2008-09-22 03:33:29

Try this:

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

The above statement converts your current format to YYYY/MM/DD, please refer to this link to choose your preferable format.

@eddiegroves 2008-09-22 03:41:19

This returns '2008/09/22' for me

@Ricardo C 2008-09-24 05:00:29

111 is the Japanese format. yyy/mm/dd

@Flea 2013-07-10 20:47:39

SELECT CONVERT(VARCHAR(10),GETDATE(),101) is mm/dd/yyyy format.

@Simon_Weaver 2013-09-14 00:34:57

if you're sorting based on the raw text value (outside of the DB) then the 'japanese' format is better

@Raphael Amoedo 2015-08-19 17:41:51

It works on SQL Server 2005.

@Spider 2017-10-21 04:02:23

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

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

 SELECT CAST(GETDATE() AS DATE)

@Amar Srivastava 2017-09-08 05:24:19

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

@bleyk 2017-10-11 03:09:30

How can I get the YEar part only?

@kaub0st3r 2016-05-24 09:36:18

On SQL Server 2000

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

@Art Schmidt 2016-05-13 21:00:11

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

@Rushda 2011-09-22 12:21:32 

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

@Somnath Muluk 2016-08-24 07:36:10

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

@xbb 2016-07-20 15:58:29

Starting from SQL SERVER 2012, you can do this:

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

@Kris Khairallah 2016-05-21 09:24:33

Date:

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

Time:

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

@Shiraj Momin 2016-05-03 07:49:22

SELECT CONVERT(date, getdate())

@BenR 2008-09-24 13:02:21

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

@misteraidan 2011-08-25 00:01:41

There is also the 'time' data type in SQL2008 which answers the other half of the question of separating date and time.

@UnhandledExcepSean 2014-07-03 12:48:46

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.

@Dr. MAF 2015-11-19 09:10:23

wt about sqlserver 2005??

@Frosty840 2017-07-31 07:24:00

@Dr.MAF Completing the circle, the pre-2008 answer is here: stackoverflow.com/questions/113045/…

@Krishnraj Rana 2016-04-13 10:46:30

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

@Zack 2016-04-20 21:20:47

Your first example still has a time component. The point of the question was how to remove that.

@Shyam Bhimani 2016-03-29 22:56:51

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;

@Chuck 2017-07-26 21:19:15

This is the command for Oracle, not MS SQL.

@David Faber 2018-03-24 12:48:50

Not only is it for Oracle, not MS SQL - it's not even correct. To get the date part only from Oracle, one would use TRUNC(SYSDATE)

@etni 2014-11-19 20:46:35 

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

@Ben 2014-11-19 21:17:52

Please explain what's going on in your answer.

@Andriy M 2014-11-19 21:47:27

This suggestion has been covered by other answers (more than once).

@abatishchev 2011-01-31 09:44:38

If using SQL 2008 and above:

select cast(getdate() as date)

@Fredrick Gauss 2012-12-13 16:10:45

Msg 243, Level 16, State 1, Line 1 Type date is not a defined system type.

@abatishchev 2012-12-13 20:01:29

@FredrickGauss: What type, Date? What version of SQL Server do you use?

@Nick 2015-09-24 19:18:42

Beware! declare @date1 datetime = '2015-09-30 20:59:59.999'; select cast(@date1 as date) returns '2015-10-01'

@abatishchev 2015-09-24 20:22:51

@Nick 2015-09-24 20:51:07

@abatishchev sorry, that should have been declare @date1 datetime = '2015-09-30 23:59:59.999';select cast(@date1 as date)

@abatishchev 2015-09-25 01:33:16

@Nick: this is the issue with DateTime. use DateTime2 instead and it works fine. sqlfiddle.com/#!6/9eecb7/2833

@Frédéric 2015-12-11 17:07:38

@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

@NicVerAZ 2015-12-29 17:04:48

One of these easy to remember SQL tricks. As Mike says, only 2008 onward but, if you find a 2005 and previous DB somewhere, you may have a lot of issues :)

@abatishchev 2015-12-29 18:04:30

@NixVerAZ I believe there are exactly 0 reasons to run SQL Server 2005 in late 2016. This is pure idiocy, isn't it? A good sign of something terribly wrong there.

@user1172173 2017-06-09 18:05:55

I like the use of the ANSI std "CAST()" - if portability is a concern (or even achievable) these days :). Also preferred if wanting to preserve precision: msdn.microsoft.com/en-us/library/ms187928.aspx

@Luc VdV 2017-10-18 07:28:57

@abatischchev - 1 reason that applies to more instances than you'd hold for possible: management refusing to approve the budget for updating antiques as long as someone manages to keep them running. Even SQL Server 2000 running on Windows 2000 is still alive. SQL Server 2005 Express: about 50 instances in my company alone (no, not mine, the one I work for).

@Luc VdV 2017-10-18 07:37:01

The '2015-09-30 20:59:59.999' issue doesn't seem to apply to Sql Server 2016 anymore.

@David Faber 2018-03-24 12:47:33

One can go even further with the ANSI standard by using SELECT CAST(CURRENT_TIMESTAMP AS DATE) (CURRENT_TIMESTAMP is equivalent to GETDATE()).

@Stephon Johns 2014-03-26 12:39:38

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

@Anderson Silva 2013-11-17 17:36:27

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

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

I holpe it is useful.

@Imad 2015-10-21 04:54:54

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

@Binitta Mary 2015-09-01 13:02:45 

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

@Gerard ONeill 2015-04-30 19:00:38

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

@Gordon Bell 2008-09-22 03:35:23 

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.

@eddiegroves 2008-09-22 03:48:48

These methods are all great, but which single one do you suggest using?

@Michael 2014-08-14 16:02:18

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

Related Questions

Sponsored Content

45 Answered Questions

[SOLVED] How do I get the current date in JavaScript?

  • 2009-10-07 11:39:02
  • Suresh Chaganti
  • 2336256 View
  • 2018 Score
  • 45 Answer
  • Tags:   javascript date

44 Answered Questions

[SOLVED] How do I import an SQL file using the command line in MySQL?

29 Answered Questions

37 Answered Questions

38 Answered Questions

[SOLVED] How do you get a timestamp in JavaScript?

27 Answered Questions

49 Answered Questions

[SOLVED] How to format a JavaScript date

14 Answered Questions

[SOLVED] DateTime2 vs DateTime in SQL Server

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

15 Answered Questions

[SOLVED] How to Delete using INNER JOIN with SQL Server?

Sponsored Content