Extract Date from datetime

Here is a quick script to extract date from a date time stamp column:

select CONVERT(DATETIME, DATEDIFF(dd,0,yourcolumn)) from yourtable

So this value in the table: 2009-11-04 13:00:01.000
Will return this value: 2009-11-04 00:00:00.000

Worked well for me. I had a process that dumped data into a history table. The process took a few minutes to run and only ran once a day. I wanted to hook up a ASP page to it and give the end user some dates to chose based on a query of the history table. I ended up using this:

select DISTINCT(CONVERT(DATETIME, DATEDIFF(dd,0,yourcolumn))) from yourtable

This gave me a list of dates in my history table. Found this date hack and other cool tricks on this page: http://wiki.lessthandot.com/index.php/SQL_Server_Programming_Hacks_-_100%2B_List


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s