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
  • 23,585
  • 40
  • 126
  • 197

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
  • 59,352
  • 21
  • 103
  • 153
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,265
  • 2
  • 23
  • 36