Generate DATE value alone in SQL Server

Question

I have a query with a column received and the value 2014-02-13 19:34:00. I fetch the date alone using DATE(received) and value is 2014-02-13

In MySQL I use the following query:

SELECT DATE(received) AS Mydate, status  
FROM Notification
WHERE project = 'proj001'  
GROUP BY Mydate 
ORDER BY received;

How to generate same value in SQL Server?

possible duplicate of [How to return the date part only from a SQL Server datetime datatype](http://stackoverflow.com/questions/113045/how-to-return-the-date-part-only-from-a-sql-server-datetime-datatype) — juergen d, Feb 03 '15 at 12:59
On an unrelated matter (may be out of your control) `WHERE project='proj001'` is not a very good way of doing things for a number of reasons. Combining a string and a number almost always results in having to split them later. Leading zeros are a real pain and can be displayed even if they are not stored. If all rows have `proj` then it is redundant & not needed, if they can have different values, create a separate type column. You can then make the digit portion an int. Indexing your string will take up more disk space and cache memory than a `ProjectType char(1), ProjectNo int` will. — KM., Feb 03 '15 at 13:39

score 4 · Accepted Answer · edited Feb 03 '15 at 13:51

4

Use cast or convert in SQL Server. Cast is ANSI standard. Convert is SqlServer specific

 select cast(received as date),status FROM Notification   -- or convert(date,received)
 WHERE project='proj001'  
 GROUP BY cast(received as date),status 
 ORDER BY cast(received as date);

edited Feb 03 '15 at 13:51

marc_s

732,580
175
1,330
1,459

answered Feb 03 '15 at 12:58

Pரதீப்

91,748
19
131
172

For using ANSI standard functions: +1 – gvee Feb 03 '15 at 13:52

score 0 · Answer 2 · answered Feb 03 '15 at 13:27

0

If you are using SQL Server 2008 and higher then

SELECT CONVERT(date, '2014-02-13 19:34:00')

if you are using an older version then

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, '2014-02-13 19:34:00'))

answered Feb 03 '15 at 13:27

Mahmoud Fawzy

24
4

Generate DATE value alone in SQL Server

2 Answers2