Extracting Date from SQL Server datetime
I was working on a project yesterday that required me to get a date from a SQL Server datetime field. I needed it to return just the date (not the time), but for the life of me I couldn't figure it out (don't worry about why, just go with me here).
I called my good friend (and local SQL Server answer-man), Will Spurgeon. As usual, he came through for me. I never would have guessed the answer:
cast(convert(varchar(100), mydatecol, 101) as datetime) AS mydatecol
In the context of a more complete SQL statement:
SELECT cast(convert(varchar(100), mydatecol, 101) as datetime) AS mydatecol
FROM mytable
WHERE id=1
Apparently, telling SQL Server to convert the datetime to a varchar and then back to a datetime does the trick. Imagine that!
Update!
Someone always has a better way. The databasejournal.com web site has an article titled "Examples of how to Calculate Different SQL Server Dates" that covers another technique for this as well as all sorts of ways to calculate dates.
Here is the solution from the article:
SELECT DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)
Don't let that keep you from reading the article though. It has plenty of other good date techniques.
SELECT convert(smalldatetime, mydatecol) AS mydatecol
FROM mytable
WHERE id=1
Jason
SELECT CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, mydatecol))) AS mydatecol
FROM mytable
WHERE id=1