In T-SQL, is there a quick way to truncate the time part for a datetime value?

In T-SQL, is there a quick way to truncate the time part for a datetime value?

E.g. convert 2011-1-1 12:23:34:567 to 2011-1-1 00:00:00:000

Thanks.
thomaszhwangAsked:
Who is Participating?
 
tim_csConnect With a Mentor Commented:
CAST(Yourdate AS DATE)
0
 
thomaszhwangAuthor Commented:
But this will make the value become 2011-1-1 instead of 2011-1-1 00:00:00:000, right?
0
 
tigin44Connect With a Mentor Commented:
select convert(datetime,convert(date, getdate()))
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
tim_csConnect With a Mentor Commented:
Missed that part.  You could cast back to a datetime.

CAST(CAST(getdate() AS DATE) AS DATETIME)
0
 
lcohanConnect With a Mentor Database AnalystCommented:
For SQL 2005 I dont think you have date datat type so you can use:

SELECT CONVERT(DATETIME,(CAST(MONTH(getdate()) AS VARCHAR(2))+'/'+CAST(DAY(getdate()) AS VARCHAR(2))+'/'+CAST(YEAR(getdate()) AS VARCHAR(4))),101)
0
 
Patrick MatthewsConnect With a Mentor Commented:
The DATE data type does not exist in SQL 2005 or earlier; it was added for 2008.

Another alternative that works in all versions:

SELECT MyDate, DATEADD(day, DATEDIFF(day, 0, MyDate), 0) AS DateWithMidnightOnly
FROM SomeTable

Open in new window

0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
Or:

SELECT CAST(CAST( dateColumn AS int) AS datetime)
0
 
thomaszhwangAuthor Commented:
Thanks guys.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.