How can i obtain the date only from the datetime column?

Question

I have following query

SELECT GETDATE()

which returns me as 2013-11-16 03:31:07.740 I want only the DATE part and omit Time part.

I have used

select convert(varchar, getdate(), 100) convertResult,100 style union
select convert(varchar, getdate(), 101),101 union
select convert(varchar, getdate(), 102),102 union
select convert(varchar, getdate(), 103),103 union
select convert(varchar, getdate(), 104),104 union
select convert(varchar, getdate(), 105),105 union
select convert(varchar, getdate(), 106),106 union
select convert(varchar, getdate(), 107),107 union
select convert(varchar, getdate(), 108),108 union
select convert(varchar, getdate(), 109),109 union
select convert(varchar, getdate(), 110),110 union
select convert(varchar, getdate(), 111),111 union
select convert(varchar, getdate(), 112),112 union
select convert(varchar, getdate(), 113),113 union
select convert(varchar, getdate(), 114),114  union
select convert(varchar, getdate(), 120),120  union
select convert(varchar, getdate(), 121),121  union
select convert(varchar, getdate(), 126),126  union
select convert(varchar, getdate(), 127),127  union
select convert(varchar, getdate(), 130),130  union
select convert(varchar, getdate(), 131),131
order by 2

But not any combination gives me the date section only.

Whats wrong with [`Convert(varchar(10),GetDate(),120)`](http://sqlfiddle.com/#!6/d41d8/11952/0) ? — Tim Schmelter, Nov 15 '13 at 21:53
FWIW I don't really like the proposed duplicate. The accepted and ridiculously upvoted answer uses a technique that is no longer necessary in SQL Server 2008+ ... it also doesn't solve this specific problem! It still returns time, it's just set to midnight... — Aaron Bertrand, Nov 15 '13 at 21:54

Aaron Bertrand · Answer 1 · 2020-05-13T02:16:04.987

Most efficient will be to keep it is a date type instead of converting it to a string:

SELECT CONVERT(DATE, GETDATE());

If you really want string output for whatever reason:

SELECT CONVERT(CHAR(10), GETDATE(), 120);

(There's no reason to use VARCHAR, and the way to get a specific length to a string is to specify a length for the string. Please read this post.)

The accepted and highly up-voted answer on the proposed duplicate - which I'm sure will eventually close this question, due to lemming factor - does not adequately address this problem.

It uses a technique that is an efficient method for SQL Server 2000 and SQL Server 2005, but not for SQL Server 2008+, since the above conversion is more efficient than doing the date math (the difference is probably negligible, but it's the principle of the thing).
It still returns time in the output (it's just at midnight instead of now). The OP here doesn't want time included in the output (whether that's the real time, or midnight, etc).

score 4 · Answer 2 · answered Nov 15 '13 at 21:51

4

Since you are on SQL Server 2008

SELECT cast(GETDATE() as DATE)

answered Nov 15 '13 at 21:51

iruvar

22,736
7
53
82

+1 that's a lot more succinct than mine, but I do tend to babble – Aaron Bertrand Nov 15 '13 at 22:07
1

lol @AaronBertrand, long may your "babbling" continue, I am sure it turns out to be edifying for many of us! – iruvar Nov 15 '13 at 22:11

score 2 · Accepted Answer · answered Nov 15 '13 at 21:50

2

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

Returns

11/15/2013

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

Returns

2013-11-15

answered Nov 15 '13 at 21:50

M.Ali

67,945
13
101
127

How can i obtain the date only from the datetime column?

3 Answers3