How can i obtain the date only from the datetime column? [duplicate]

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.

Answer 1

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.

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

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

Answer 2

Since you are on SQL Server 2008

SELECT cast(GETDATE() as DATE)