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

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s