How to select date without time in SQL

Question

When I select date in SQL it is returned as 2011-02-25 21:17:33.933. But I need only the Date part, that is 2011-02-25. How can I do this?

I guess he wants a string, and therefore it is no duplicated — bernd_k, Feb 26 '11 at 08:01
Possible duplicate of [How to return the date part only from a SQL Server datetime datatype](https://stackoverflow.com/questions/113045/how-to-return-the-date-part-only-from-a-sql-server-datetime-datatype) — TylerH, Sep 19 '17 at 20:57
@TylerH there is any way to get 2011-02-25 00:00:00.000 instead of current time ? — Thrainder, Jan 12 '20 at 14:37

score 623 · Answer 1 · edited Jul 26 '18 at 17:41

623

For SQL Server 2008:

Convert(date, getdate())

Please refer to https://docs.microsoft.com/en-us/sql/t-sql/functions/getdate-transact-sql

edited Jul 26 '18 at 17:41

hammythepig

925
1
8
29

answered Nov 17 '11 at 02:18

Prad9

6,327
2
12
2

6

I get "Type date is not a defined system type." – SeaDrive May 31 '12 at 18:49
35

i have no idea why this is such an upvoted answer. this question is for SQL Server 2005, *NOT* 2008. 2005 doesn't have the `date` data-type therefore rendering this solution as invalid. – Joshua Burns Oct 17 '14 at 20:01
107

It's upvoted because people come looking for solutions to their problem, not the authors. So if 110 people found this worked for them I think it's fair that it has 110 upvotes. – James Jun 18 '15 at 11:37
I have same error when i use select convert (table.order_date , getdate()); sql server version 2017 , the error (Type date is not a defined system type) the column type datetime – Abdullah May 27 '20 at 11:43
This will only give you the current date. What about other dates stored in the db? – Zizzipupp Nov 02 '20 at 17:26
This works fine for me in SQL Server 2017 – Jon Story Mar 29 '21 at 16:50
1

@zizzipupp just replace the text `getdate()` with the actual name of whatever column you want to convert – JakeMc May 26 '21 at 14:19

score 170 · Accepted Answer · edited Feb 26 '20 at 18:04

170

I guess he wants a string.

select convert(varchar(10), '2011-02-25 21:17:33.933', 120)

120 here tells the convert function that we pass the input date in the following format: yyyy-mm-dd hh:mi:ss.

edited Feb 26 '20 at 18:04

Павле

690
4
15

answered Feb 26 '11 at 07:57

bernd_k

11,018
7
41
62

What is 120 for? – Павле Jan 20 '20 at 12:57
@Павле refer this article [Date and Time styles](https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15#date-and-time-styles) – Fox Vĩnh Tâm Feb 26 '20 at 09:04
i used this select convert(varchar(10), APPROVED_DATE, 120) , i got error column APPROVED_DATE datetime , how i will convert it ? error The multi-part identifier "LAB_RESULTS.APPROVED_DATE" could not be bound. – Abdullah May 27 '20 at 11:51
This works fine as long as you are not going to put it into a group function and try and order by the date. Prad9's answer will work better for this – Recnats Aug 13 '21 at 09:37

score 64 · Answer 3 · edited Dec 24 '17 at 19:40

64

The fastest is datediff, e.g.

select dateadd(d, datediff(d,0, [datecolumn]), 0), other..
from tbl

But if you only need to use the value, then you can skip the dateadd, e.g.

select ...
WHERE somedate <= datediff(d, 0, getdate())

where the expression datediff(d, 0, getdate()) is sufficient to return today's date without time portion.

edited Dec 24 '17 at 19:40

Kolappan N

2,875
2
31
38

answered Feb 26 '11 at 07:32

RichardTheKiwi

102,799
24
193
261

Oldie but goodie, used this trick dozens of times in older 2000/2005 DBs. – KeithS May 25 '17 at 19:45

score 61 · Answer 4 · edited Feb 15 '22 at 22:11

61

Using CAST(GETDATE() As Date) worked for me

edited Feb 15 '22 at 22:11

KyleMit

35,223
60
418
600

answered Jul 27 '14 at 21:31

Anna-leny

627
5
2

where i will add my column name ? select cast(getdate() as date , order_date)? – Abdullah May 27 '20 at 11:47
2

@Abdullah `CAST(order_date AS DATE)` – Andrew Morton Jun 18 '20 at 10:22
Found the same in article https://learnsql.com/cookbook/how-to-get-the-current-date-without-time-in-t-sql/ – Jimit.Gandhi Nov 10 '21 at 10:35

score 27 · Answer 5 · edited Oct 21 '14 at 08:48

27

CAST(
        FLOOR( 
             CAST( GETDATE() AS FLOAT ) 
        )

AS DATETIME
)

http://www.bennadel.com/blog/122-Getting-Only-the-Date-Part-of-a-Date-Time-Stamp-in-SQL-Server.htm

edited Oct 21 '14 at 08:48

Stefan Steiger

73,615
63
359
429

answered Feb 26 '11 at 07:19

Brandon Frohbieter

16,827
3
36
61

2

The most elegant answer on this page. – Mike Chamberlain Feb 28 '15 at 07:30
1

This doesn't work when you replace GETDATE() with a value of type DATETIME2. – Noel Widmer May 19 '21 at 09:57

score 14 · Answer 6 · answered Sep 09 '14 at 14:06

14

For 2008 older version :

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

answered Sep 09 '14 at 14:06

Domcha

141
2
5

applauses for this! The others solutions only rely on the varchar(10) part, which just truncates the value. – Gonza Oviedo Oct 03 '17 at 18:13

A.Goutam · Answer 7 · 2015-09-16T12:22:41.763

12

you can use like this

SELECT Convert(varchar(10), GETDATE(),120)

edited Sep 16 '15 at 12:22

answered Sep 07 '12 at 09:43

A.Goutam

3,261
8
36
85

where i will put my column name order date ? – Abdullah May 27 '20 at 11:53

Simon Alphonse · Answer 8 · 2020-01-03T13:34:06.610

6

In case if you need the time to be zeros like 2018-01-17 00:00:00.000:

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

edited Jan 03 '20 at 13:34

answered Jul 29 '19 at 07:36

Simon Alphonse

61
1
3

Thanks, this was what I was looking for. – Pratik Ghag Sep 20 '19 at 07:16

score 6 · Answer 9 · answered Jan 21 '21 at 08:12

I would use DATEFROMPARTS function. It is quite easy and you don't need casting. As an example this query :

Select  DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), DAY(GETDATE())) as myNewDate

will return

2021-01-21

The good part you can also create you own date, for example you want first day of a month as a date, than you can just use like below:

Select  DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1) as myNewDate

The result will be:

2021-01-01

score 3 · Answer 10 · answered Oct 24 '17 at 18:01

3

You can try this one too.

SELECT CONVERT(DATE, GETDATE(), 120)

answered Oct 24 '17 at 18:01

Ram Pratap

979
9
8

score 3 · Answer 11 · answered Jan 06 '20 at 11:56

3

Its too late but following worked for me well

declare @vCurrentDate date=getutcdate()

select @vCurrentDate

When data type is date, hours would be truncated

answered Jan 06 '20 at 11:56

Naveed Yousaf

414
4
14

Stefan Steiger · Answer 12 · 2014-10-21T08:49:11.363

2

It's a bit late, but use the ODBC "curdate" function (angle brackes 'fn' is the ODBC function escape sequence).

SELECT {fn curdate()}

Output: 2013-02-01

edited Oct 21 '14 at 08:49

answered Feb 01 '13 at 16:21

Stefan Steiger

73,615
63
359
429

4

I am honestly curious on how you think your answer is better than the accepted answer. Or where you perhaps referring to the other answers? – Mikael Eriksson Feb 01 '13 at 17:00
1

@Mikael Eriksson: Hmm, because ODBC-Functions are canonical functions and therefore indexable, unlike the nondeterministic SQL-Server functions. But never mind, it's only a scaling issue when you move from your 3 entries in testing to the 1 *10E6 entries in production, you get no problems during development ;) – Stefan Steiger Feb 07 '13 at 08:35
8

If you run this query `SELECT {fn curdate()} FROM (SELECT 1) AS T(X)` and have a look at the actual execution plan (xml version) you will see that what is actually executed is `CONVERT(varchar(10),getdate(),23)`. So the datatype of this ODBC function is `varchar(10)` which means that if you want to compare the result with a `datetime` you will get an implicit conversion from `varchar(10)` to `datetime` on a string `yyyy-mm-dd`. That implicit conversion will fail with `set dateformat dmy`. – Mikael Eriksson Feb 07 '13 at 09:24
1

@Mikael Eriksson: Looks like a bug, they should be using SELECT CONVERT(char(8), GETDATE(), 112) instead. – Stefan Steiger Oct 21 '14 at 08:53
"canonical functions and therefore indexable, unlike the nondeterministic SQL-Server functions" -- er, what? – Martin Smith Sep 12 '15 at 12:01

score 2 · Answer 13 · answered Apr 15 '18 at 21:48

2

Convert it back to datetime after converting to date in order to keep same datatime if needed

select Convert(datetime, Convert(date, getdate())  )

answered Apr 15 '18 at 21:48

RollRoll

7,737
18
70
122

score 1 · Answer 14 · edited Aug 26 '15 at 08:21

Use is simple:

convert(date, Btch_Time)

Example below:

Table:

Efft_d       Loan_I  Loan_Purp_Type_C   Orig_LTV    Curr_LTV    Schd_LTV    Un_drwn_Bal_a      Btch_Time            Strm_I  Btch_Ins_I
2014-05-31  200312500   HL03             NULL         1.0000    1.0000         1.0000      2014-06-17 11:10:57.330  1005    24851e0a-53983699-14b4-69109

Select * from helios.dbo.CBA_SRD_Loan where Loan_I in ('200312500') and convert(date, Btch_Time) = '2014-06-17'

score 1 · Answer 15 · edited Mar 26 '15 at 19:17

1

If you want to return a date type as just a date use

CONVERT(date, SYSDATETIME())

or

SELECT CONVERT(date,SYSDATETIME())

or

DECLARE @DateOnly Datetime
SET @DateOnly=CONVERT(date,SYSDATETIME())

edited Mar 26 '15 at 19:17

TYY

2,642
1
12
14

answered Mar 26 '15 at 18:13

TMP ByKIS

35
1

score 1 · Answer 16 · answered Dec 15 '20 at 15:48

1

select DATE(field) from table;

field value: 2020-12-15 12:19:00

select value: 2020-12-15

answered Dec 15 '20 at 15:48

No Name

11
3

Is this an MSSQL Server function? I checked Microsoft Docs but couldn't find it. – bafsar Apr 18 '22 at 13:24

score 0 · Answer 17 · answered Jan 30 '14 at 18:25

0

In PLSQL you can use

to_char(SYSDATE,'dd/mm/yyyy')

answered Jan 30 '14 at 18:25

Morterox

27
1

consider transfering this to a comment. – xlembouras Jan 30 '14 at 18:42
This question is about SQL Server... not Oracle – Ben Jan 30 '14 at 18:42

score 0 · Answer 18 · edited Oct 20 '14 at 16:18

0

First Convert the date to float (which displays the numeric), then ROUND the numeric to 0 decimal points, then convert that to datetime.

convert(datetime,round(convert(float,orderdate,101),0) ,101)

edited Oct 20 '14 at 16:18

RickyA

14,690
5
65
93

answered Oct 20 '14 at 15:00

user4162468

9

`round` needs a third parameter "1" to truncate instead of round, otherwise when time is past noon it will get rounded up to the next day. – Andrius R. Jan 12 '21 at 10:55

score 0 · Answer 19 · answered Oct 24 '17 at 17:58

0

Try this.

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

answered Oct 24 '17 at 17:58

Ram Pratap

979
9
8

How to select date without time in SQL

19 Answers19

Linked

Related