0

Possible Duplicate:
Best approach to remove time part of datetime in SQL Server

Suppose a table column MyDate is datetime. then following data can be saved to db: 

2011-11-24 12:43:27.723  
2011-11-24 12

I want to convert 2011-11-24 12:43:27.723 to 2011-11-24 with no time, I tried following way:

CONVERT(DATETIME, CONVERT(INT, MyDate))

Interesting is: 2011-11-24 12:43:27.723 is converted to 2011-11-25 instead of 2011-11-24.

How to resolve it?

Community
  • 1
  • 1
KentZhou
  • 24,805
  • 41
  • 134
  • 200

2 Answers2

4

Use built in date type which is available since Sql Server 2008:

SELECT CAST('2011-11-24 12:43:27.723' as Date)

Output:

2011-11-24
sll
  • 61,540
  • 22
  • 104
  • 156
0

Although the question is regarding SQL 2008 if you are using an earlier version of SQL Server I tend to use this approach:

CAST(FLOOR(CAST(CAST('2011-11-24 12:43:27.273' AS DATETIME) AS FLOAT)) AS DATETIME)

Which outputs:

2011-11-24 00:00:00.000
Paul Hunt
  • 3,395
  • 2
  • 24
  • 39