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

Question

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?

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: http://www.sql-server-performance.com/articles/dev/datetime_2008_p1.aspx — Ben Hoffstein, Sep 22 '08 at 03:44
Don't miss [this post](http://stackoverflow.com/questions/2775/whats-the-best-way-to-remove-the-time-portion-of-a-datetime-value-sql-server/3696991#3696991) showing performance testing results of the various time-removal methods. — ErikE, Aug 17 '12 at 22:02
@Martin weird, thanks, [let's try again](http://www.sqlperformance.com/2012/09/t-sql-queries/what-is-the-most-efficient-way-to-trim-time-from-datetime). — Aaron Bertrand, Nov 17 '13 at 21:13
Don't be mislead by the votes and accepted answer, Take a look at http://stackoverflow.com/a/126984/1155650 — Rohit Vipin Mathews, Nov 26 '13 at 10:24
@hktegner - it must be misleading for those who have. Also there is one version below 2008 (ie:2005) where as after it we have 4 and counting. — Rohit Vipin Mathews, Dec 14 '13 at 05:11
@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
For future readers: https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/ is also useful for getting the correct format when using convert. — eaglei22, Dec 09 '19 at 14:31

score 2832 · Accepted Answer · edited Mar 25 '23 at 04:34

2832

NOTE: This answer returns the original DATETIME or DATETIME2 type. For an expression that returns a true DATE type (SQL Server 2008 and later), see BenR's answer below.

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

edited Mar 25 '23 at 04:34

T N

4,322
1
5
18

answered Sep 22 '08 at 03:34

aku

122,288
32
173
203

Is this way better or worse performance wise than using the convert methods other have suggested? Or is it negligible? – Eddie Groves Sep 22 '08 at 03:48
1

My method works faster. It doesn't require conversions to varchar and allows efficient date calculations – aku Sep 22 '08 at 03:54
63

+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 04:04
1

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. – Cade Roux Sep 22 '08 at 04:11
8

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 08:25
+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 Sep 12 '10 at 23:15
2

Also don't miss [this post](http://stackoverflow.com/questions/2775/whats-the-best-way-to-remove-the-time-portion-of-a-datetime-value-sql-server/3696991#3696991) showing performance testing results. – ErikE Sep 13 '10 at 00:51
41

@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](http://msdn.microsoft.com/en-us/library/aa226054%28v=sql.80%29.aspx) for more info. – ErikE Aug 17 '12 at 22:03
1

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 – N t May 20 '13 at 14:10
1

@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` – Praveen Jun 15 '13 at 11:13
8

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

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`](http://msdn.microsoft.com/en-us/library/ms174420.aspx) keyword to truncate your `datetime` at an arbitrary level. – Michael Aug 14 '14 at 16:08
1

select cast(createddate as date) from table where createdate is your datetime column – karthik kasubha Mar 24 '19 at 17:34
The best part of this method is that you can fine-tune its granularity. You can strip seconds, minutes, "floor" the date to week, month, you can even modify it (by manipulating the `DATEDIFF` output) to round to nearest 5th minute, to nearest noon... – andowero Jul 15 '22 at 08:41

BenR · Answer 2 · 2016-04-18T15:03:53.137

908

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

edited Apr 18 '16 at 15:03

answered Sep 24 '08 at 13:02

BenR

11,296
3
28
47

47

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 00:01
10

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. – UnhandledExcepSean Jul 03 '14 at 12:48
2

wt about sqlserver 2005?? – Dr. MAF Nov 19 '15 at 09:10
@Dr.MAF Completing the circle, the pre-2008 answer is here: https://stackoverflow.com/questions/113045/how-to-return-the-date-part-only-from-a-sql-server-datetime-datatype – Frosty840 Jul 31 '17 at 07:24
In SQL 2019, what is preferred - this answer or CAST or CONVERT? – variable Feb 02 '22 at 06:26

abatishchev · Answer 3 · 2016-03-29T23:11:59.240

231

If using SQL 2008 and above:

select cast(getdate() as date)

edited Mar 29 '16 at 23:11

answered Jan 31 '11 at 09:44

abatishchev

98,240
88
296
433

Msg 243, Level 16, State 1, Line 1 Type date is not a defined system type. – Fredrick Gauss Dec 13 '12 at 16:10
3

@FredrickGauss: What type, Date? What version of SQL Server do you use? – abatishchev Dec 13 '12 at 20:01
8

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
@abatishchev sorry, that should have been `declare @date1 datetime = '2015-09-30 23:59:59.999';select cast(@date1 as date)` – Nick Sep 24 '15 at 20:51
8

@Nick: this is the issue with DateTime. use `DateTime2` instead and it works fine. http://sqlfiddle.com/#!6/9eecb7/2833 – abatishchev Sep 25 '15 at 01:33
10

@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-01`too! `declare @date1 datetime = '2015-09-30 23:59:59.999';select @date1` => `2015-10-01` – Frédéric Dec 11 '15 at 17:07
5

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 :) – NicVerAZ Dec 29 '15 at 17:04
1

@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. – abatishchev Dec 29 '15 at 18:04
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 – galaxis Jun 09 '17 at 18:05
@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 Oct 18 '17 at 07:28
The '2015-09-30 20:59:59.999' issue doesn't seem to apply to Sql Server 2016 anymore. – Luc VdV Oct 18 '17 at 07:37
3

One can go even further with the ANSI standard by using `SELECT CAST(CURRENT_TIMESTAMP AS DATE)` (`CURRENT_TIMESTAMP` is equivalent to `GETDATE()`). – David Faber Mar 24 '18 at 12:47
I prefer this over the `CONVERT()` syntax as it's more standards compliant (though, of course, `CURRENT_TIMESTAMP`) – Auspex Sep 16 '21 at 08:55

score 88 · Answer 4 · edited Mar 02 '22 at 12:05

DATEADD and DATEDIFF are better than CONVERTing to varchar. Both queries have the same execution plan, but execution plans are primarily 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 because 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 row set 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.

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 05: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 05: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 06:20
Aku, same results. No difference in performance over ten million rows. — Ricardo C, Sep 25 '08 at 03:41
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

score 61 · Answer 5 · edited Sep 14 '18 at 11:10

61

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.

edited Sep 14 '18 at 11:10

Vladimir Vagaytsev

2,871
9
33
36

answered Sep 22 '08 at 03:33

Nescio

27,645
10
53
72

5

This returns '2008/09/22' for me – Eddie Groves Sep 22 '08 at 03:41
3

SELECT CONVERT(VARCHAR(10),GETDATE(),101) is `mm/dd/yyyy` format. – Flea Jul 10 '13 at 20:47
4

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 00:34

score 50 · Answer 6 · edited Jan 31 '11 at 09:45

50

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

edited Jan 31 '11 at 09:45

abatishchev

98,240
88
296
433

answered Sep 22 '08 at 03:33

Cade Roux

88,164
40
182
265

John Sonnino · Answer 7 · 2022-02-11T12:28:22.887

32

Just do:

SELECT CAST(date_variable AS date)

or with with PostgreSQL:

SELECT date_variable::date

This is called typecasting btw!

edited Feb 11 '22 at 12:28

answered Jan 15 '20 at 10:08

John Sonnino

509
5
10

Mahesh ML · Answer 8 · 2014-08-27T08:36:04.860

30

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)

edited Aug 27 '14 at 08:36

answered Dec 19 '13 at 06:48

Mahesh ML

542
9
16

1

This returns me date with zero time, not just date – Bohemian Jan 05 '14 at 13:09
1

can i know which version if sql server you are using? – Mahesh ML Mar 19 '14 at 07:15
1

@MaheshML it returns both date and time in MS SQL 2012. – Marek Apr 13 '14 at 15:50
1

Works like a charm in SQL Azure – Martín Coll May 21 '14 at 15:01
5

@MaheshML There is no such thing as SQL Server 2010. – SvenAelterman Sep 25 '17 at 02:22
1

I expect "SQL Server 2010" means SQL Server 2008 R2 (it was *released* in 2010) – J. Chris Compton Jun 05 '19 at 20:19

score 25 · Answer 9 · edited Apr 14 '15 at 11:45

25

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

edited Apr 14 '15 at 11:45

ughai

9,830
3
29
47

answered Sep 22 '08 at 03:34

DaveK

4,509
3
33
33

score 24 · Answer 10 · edited Nov 07 '19 at 15:48

If you need the result as a varchar, 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 use any of the queries below

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 111)) AS OnlyDate

2014-03-26 00:00:00.000

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 112)) AS OnlyDate

2014-03-26 00:00:00.000

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

2014-03-26 00:00:00.000

Somnath Muluk · Answer 11 · 2016-09-20T05:26:36.440

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:

To skip the culture, the custom formats lets you set your own, e.g. `FORMAT (@d, 'yyyyy-MM-dd')` to get 2011-10-11. — ourmandave, Jan 07 '21 at 13:43
Using FORMAT in any case in SQL Server is at least 20 times slower than even some of the craziest conversions that you can think of. I'd stay away from it for everything. — Jeff Moden, May 05 '22 at 04:02

score 18 · Answer 12 · edited Jan 27 '17 at 21:10

18

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

edited Jan 27 '17 at 21:10

MDM

897
6
12

answered Sep 22 '11 at 12:21

Rushda

197
1
2

score 17 · Answer 13 · answered Sep 22 '08 at 07:38

17

Using FLOOR() - just cut time part.

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

answered Sep 22 '08 at 07:38

DiGi

2,528
18
26

4

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](http://stackoverflow.com/questions/2775/whats-the-best-way-to-remove-the-time-portion-of-a-datetime-value-sql-server/3696991#3696991) for more detail. – ErikE Sep 12 '10 at 23:16

score 15 · Answer 14 · edited Mar 29 '16 at 22:42

15

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.

edited Mar 29 '16 at 22:42

chancrovsky

582
3
11

answered Sep 22 '08 at 03:35

Gordon Bell

13,337
3
45
64

1

These methods are all great, but which single one do you suggest using? – Eddie Groves Sep 22 '08 at 03:48
3

Note that the "correct" version of the top two is `select dateadd(dd, datediff(dd, 0, getdate()), 0)`, because the `dd`s can then be swapped out for any of [the `datepart` keywords](http://msdn.microsoft.com/en-us/library/ms174420.aspx) 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
be aware, that with the new DATETIME2 this truncation doesn't work every time correctly: `DECLARE @myDate DATETIME2 = '2020-12-31 23:59:59.998911';` `SELECT @myDate AS orig_date, DATEADD(dd, 0, DATEDIFF(dd, 0, @myDate)) AS truncated_date` It results as orig_date: **2020-12-31 23:59:59.9989110** truncated_date: **2021-01-01 00:00:00.000** – Tom BK Cz Apr 19 '23 at 22:24

score 15 · Answer 15 · answered Jul 26 '12 at 20:00

15

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 Jul 26 '12 at 20:00

Focusyn

159
1
2

1

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
1

@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 05:09
1

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

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

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

@Andrew again, that's fine. My point - ***AGAIN*** - is that you should never have to cast ***the column***. [Please read](https://sqlblog.org/blogs/aaron_bertrand/archive/2009/10/16/bad-habits-to-kick-mishandling-date-range-queries.aspx). – Aaron Bertrand Nov 18 '13 at 23:56

score 15 · Answer 16 · edited Mar 29 '16 at 22:54

15

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

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

I holpe it is useful.

edited Mar 29 '16 at 22:54

chancrovsky

582
3
11

answered Nov 17 '13 at 17:36

Anderson Silva

384
2
6

Art Schmidt · Answer 17 · 2017-05-03T16:05:44.103

12

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

edited May 03 '17 at 16:05

answered May 13 '16 at 21:00

Art Schmidt

141
1
5

score 12 · Answer 18 · answered May 21 '16 at 09:24

12

Date:

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

Time:

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

answered May 21 '16 at 09:24

Kris Khairallah

1,537
13
16

score 11 · Answer 19 · edited Jun 06 '15 at 12:27

11

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

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

edited Jun 06 '15 at 12:27

Imran Ali Khan

8,469
16
52
77

answered May 12 '14 at 14:41

Ankit Khetan

138
1
6

yusuf hayırsever · Answer 20 · 2021-03-09T12:34:52.173

11

Syntax:

SELECT CONVERT (data_type(length)),Date, DateFormatCode)

Ex:

Select CONVERT(varchar,GETDATE(),1) as [MM/DD/YY]
Select CONVERT(varchar,GETDATE(),2) as [YY.MM.DD]

all dateformatcodes about Date:

DateFormatCode  Format
1       [MM/DD/YY]
2       [YY.MM.DD]
3       [DD/MM/YY]
4       [DD.MM.YY]
5       [DD-MM-YY]
6       [DD MMM YY]
7       [MMM DD,YY]
10      [MM-DD-YY]
11      [YY/MM/DD]
12      [YYMMDD]
23      [yyyy-mm-dd]
101     [MM/DD/YYYY]
102     [YYYY.MM.DD]
103     [DD/MM/YYYY]
104     [DD/MM/YYYY]
105     [DD/MM/YYYY]
106     [DD MMM YYYY]
107     [MMM DD,YYYY]
110     [MM-DD-YYYY]
111     [YYYY/MM/DD]
112     [YYYYMMDD]

edited Mar 09 '21 at 12:34

answered Mar 09 '21 at 12:28

yusuf hayırsever

691
7
19

+1 for the neat info (maybe not performance optimized but clear and useful). It would be even better if you could post a link to the reference for the conversion codes. Optimally, if possible, present a way to pick ones own, customizable format (e.g. how to get *dd-yy-mm*) as well as incorporating the time part in the format (e.g. how to get *yyyy-hh-ss*, however weird that would be). – Konrad Viltersten Aug 07 '22 at 06:27

score 9 · Answer 21 · edited Sep 08 '17 at 09:01

9

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

edited Sep 08 '17 at 09:01

Eric Aya

69,473
35
181
253

answered Sep 08 '17 at 05:24

Amar Srivastava

373
3
10

score 9 · Answer 22 · answered Nov 24 '19 at 08:00

9

In this case, date only, you we are gonna run this query:

SELECT CONVERT(VARCHAR(10), getdate(), 111);

answered Nov 24 '19 at 08:00

Mohammad Neamul Islam

283
6
6

score 8 · Answer 23 · edited Nov 27 '13 at 06:44

8

I think this would work in your case:

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

edited Nov 27 '13 at 06:44

Radim Köhler

122,561
47
239
335

answered Nov 27 '13 at 06:16

bishnu karki

129
1
5

score 8 · Answer 24 · edited Mar 30 '16 at 00:21

8

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

edited Mar 30 '16 at 00:21

chancrovsky

582
3
11

answered Nov 19 '14 at 20:46

etni

81
1
2

2

This suggestion has been covered by other answers (more than once). – Andriy M Nov 19 '14 at 21:47

score 8 · Answer 25 · answered Apr 13 '16 at 10:46

8

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 Apr 13 '16 at 10:46

Krishnraj Rana

6,516
2
29
36

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

score 8 · Answer 26 · answered Jul 20 '16 at 15:58

8

Starting from SQL SERVER 2012, you can do this:

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

answered Jul 20 '16 at 15:58

xbb

2,073
1
19
34

score 7 · Answer 27 · answered Feb 27 '15 at 21:16

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

score 6 · Answer 28 · answered May 17 '14 at 05:46

6

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 May 17 '14 at 05:46

Janaka R Rajapaksha

3,585
1
25
28

score 6 · Answer 29 · edited Mar 29 '16 at 22:52

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

score 6 · Answer 30 · answered Jan 15 '16 at 18:14

6

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 Jan 15 '16 at 18:14

lit

14,456
10
65
119

`select {fn current_date()} as today` works for me. – brianary Dec 02 '19 at 18:26
@brianary - That's nice, but it is not ANSI SQL. – lit Dec 08 '19 at 17:28
That's fair enough, and your answer is nicely portable, but I figured as long as we're working around T-SQL, this also works (and shows that implementing ANSI CURRENT_DATE would be trivial for MS). – brianary Dec 08 '19 at 18:28

score 5 · Answer 31 · edited Mar 29 '14 at 16:12

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.

score 5 · Answer 32 · edited Jun 21 '17 at 06:27

5

On SQL Server 2000

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

edited Jun 21 '17 at 06:27

Jonathan Leffler

730,956
141
904
1,278

answered May 24 '16 at 09:36

r-magalhaes

427
2
9
18

score 4 · Answer 33 · answered Jul 08 '15 at 06:28

4

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

answered Jul 08 '15 at 06:28

Surekha

87
6

1

The question states SQL Server. This seems like MySQL? – The1nk Dec 17 '15 at 18:31

score 4 · Answer 34 · edited Mar 29 '16 at 22:53

4

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

edited Mar 29 '16 at 22:53

chancrovsky

582
3
11

answered Sep 01 '15 at 13:02

Binitta Mary

134
3

Zhorov · Answer 35 · 2022-09-01T08:58:15.173

4

Starting from SQL Server 2022 (16.x), another option is DATETRUNC() function using day as value of datepart parameter:

SELECT DATETRUNC(day, GETDATE());

edited Sep 01 '22 at 08:58

answered Sep 01 '22 at 07:00

Zhorov

28,486
6
27
52

score 3 · Answer 36 · answered Oct 21 '17 at 04:02

3

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

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

 SELECT CAST(GETDATE() AS DATE)

answered Oct 21 '17 at 04:02

Spider

514
1
10
22

score 3 · Answer 37 · answered Mar 24 '19 at 17:36

3

select cast(createddate as date) as derivedate from table

createdate is your datetime column , this works for sqlserver

answered Mar 24 '19 at 17:36

karthik kasubha

392
2
13

score 3 · Answer 38 · answered Jul 30 '19 at 10:32

3

If you want the date to show 2008-09-22 00:00:00.000

then you can round it using

SELECT CONVERT(datetime, (ROUND(convert(float, getdate()-.5),0)))

This will show the date in the format in the question

answered Jul 30 '19 at 10:32

ChrisM

505
6
18

score 2 · Answer 39 · answered Nov 21 '18 at 11:39

2

My Style

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

answered Nov 21 '18 at 11:39

CAGDAS AYDIN

61
1
8

score 1 · Answer 40 · edited Mar 30 '19 at 11:50

1

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

SELECT CONVERT(datetime, CONVERT(varchar, GETDATE(), 103)) -----dd/mm/yyyy
SELECT CONVERT(datetime, CONVERT(varchar, GETDATE(), 101))------mm/dd/yyyy
SELECT CONVERT(datetime, CONVERT(varchar, GETDATE(), 102))

edited Mar 30 '19 at 11:50

ppreetikaa

1,149
2
15
22

answered Mar 30 '19 at 11:01

ankit soni

35
8

score 1 · Answer 41 · answered Apr 23 '19 at 17:56

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.

The last 3 are full dateTime stamps being truncated. 121 is not separated by a 'T' while the other two are. 127 is more precise when working with DATETIME2 formats, but with regular datetimes, they round to the same values; so, in a different context, these return 3 different datetime values. As for the second one, A = B can be used in slightly different ways than A AS B using other functions than this, but using these functions, they work the same. As for Convert vs Cast, Cast is a native ANSI function while Convert is SQL specific. Convert has more options but not always as performant. — Nosajimiki, Jul 16 '21 at 17:18

Hongjin Zhu · Answer 42 · 2023-02-15T07:01:00.797

0

SELECT CONVERT(varchar(100), GETDATE(), 102); --2023.02.15
SELECT CONVERT(varchar(100), GETDATE(), 23);  --2023-02-15

you can fllow this url to find some other format

example:

https://www.cnblogs.com/wintuzi/p/16164124.html

hope i can help you

edited Feb 15 '23 at 07:01

answered Feb 15 '23 at 06:54

Hongjin Zhu

17
4

score -1 · Answer 43 · edited Apr 05 '16 at 21:40

-1

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;

edited Apr 05 '16 at 21:40

Stanislav

2,629
1
29
38

answered Mar 29 '16 at 22:56

Shyam Bhimani

1,310
1
22
37

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)` – David Faber Mar 24 '18 at 12:48

score -1 · Answer 44 · edited Sep 13 '19 at 11:43

-1

select convert(getdate() as date)

select CONVERT(datetime,CONVERT(date, getdate()))

edited Sep 13 '19 at 11:43

Nikhil Wagh

1,376
1
24
44

answered Oct 05 '18 at 08:18

mokh223

544
3
5
14

I think you have copied in 2 select statements but copied the second select inside the first select. – ChrisM Jul 29 '19 at 12:53
Just my 2 cents worth, but the first SELECT statement: “select convert(getdate() as date)” does not work. The “date” and “getdate()” should have been inverted like in the code below. “SELECT CONVERT(DATE, GETDATE() )” You could also use the “CAST()” function as well. Either one will return the same results. “SELECT CAST(GETDATE() AS DATE)” – Aubrey Love Sep 29 '21 at 15:24

score -1 · Answer 45 · answered Feb 07 '20 at 16:52

As there has been many changes since this question had answers, I wanted to provide a new way to get the requested result. There are two ways to parse DATETIME data. First, to get the date as this question asks:

DATEVALUE([TableColumnName])

Second, to get the time from the value:

TIMEVALUE([TableColumnName])

Example:

Table: Customers

Column: CreationDate as DateTime

[Customers].[CreationDate]: 2/7/2020 09:50:00

DATEVALUE([Customers].[CreationDate]) '--> Output: 2/7/2020
TIMEVALUE([Customers].[CreationDate]) '--> Output: 09:50:00

I hope that this helps as I was searching for a while and found many answers as seen in this question and none of those worked. IE CAST and CONVERT.

Happy Coding!

score -2 · Answer 46 · answered May 28 '19 at 08:34

-2

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

answered May 28 '19 at 08:34

Jithin Joy

118
4

1

DATE is not a function dude. – Rohan Rao Jan 06 '20 at 09:39
1

To clarify... DATE() is a function in mySQL, but this question is about SQL Server. In mySQL you can use the DATE() function to extract the date from a date time element such that DATE("2021-06-15 09:34:21") returns "2021-06-15", but GETDATE() is not a mySQL function, so he is mix-matching SQL languages. The equivalent of this in mySQL would be DATE(NOW())... but mySQL makes this completely unnecessary since you can just call CURDATE() instead. – Nosajimiki Jul 16 '21 at 16:53
`DATE` is a function in [snowflake](https://docs.snowflake.com/en/sql-reference/functions/to_date). – MackM Mar 29 '23 at 00:18

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

46 Answers46

If you are using SQL Server 2012 or above versions,

1."d" - Short date pattern.

2."D" - Long date pattern.

Linked

Related