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!


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.

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
Wouldn't this provide the same functionality with less overhead?

SELECT convert(smalldatetime, mydatecol) AS mydatecol
FROM mytable
WHERE id=1


# Posted By | 2/3/05 4:29 AM
Sorry Jason, I just tested it and it didn't work.
# Posted By | 2/3/05 5:29 PM
Thanks, your code is works!
# Posted By satiri | 10/24/05 4:22 AM
This method runs much faster, especially when running a large number of records

FROM mytable
WHERE id=1
# Posted By Jim Banks | 10/30/06 4:02 PM
Works Like a charm!!! just in time to save me from a major headache
# Posted By Spice and Gold Coins | 12/14/06 3:24 PM
BlogCFC was created by Raymond Camden. This blog is running version 5.8.001.