Chopping off the time in a DateTime field in SQL

Sometimes you need to chop off the time piece of a datetime field, usually in a report. The most common and natural way to do this is to set up the report and change the display format to show date and not the times. But what if you need to group your SQL by date, regardless of time? What if you want to sort by date, then by category? If your dates have times in them, the category won’t matter for the most part.

Well I ran across the situation today where we needed items grouped by dates, then sorted by category. 

For SQL Server 2008, you can cast as a Date. Finally.

CAST(MyDateField) AS date

For SQL Server 2005, the key is quite simple. A datetime value in SQL Server is just a float in disguise. So cast your date into a float, floor it, and cast back to a date. It works amazingly well.

cast(floor(cast(MyDateField as float)) as datetime)

Here’s a screenshot of a sql query that shows before and after values, side-by-side.

Let’s say you wanted to remove the date. That is easy as well. The SQL below should explain it. The result is a float, the fraction of the day that represents time. We don’t have  a time fieldtype, so we can show this as a float or convert it to a string. I’ll leave the string conversion up to the reader, since I don’t need it for this exercise.

To verify the output, let’s do a little math. 6am is 25% of the day, so .25 checks out. 8:30 am is 8.5 hours of 24, so .35416666 is confirmed.

So there it is, very simple and very efficient SQL to remove the timestamp from a date field in a query.

Advertisements

Tags:

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

%d bloggers like this: