Archive

Posts Tagged ‘Date Datetime SQL’

Removing time from SQL datetime

August 6, 2009 6 comments

Quick Date and time SQL

August 6th 2009 | Kevin Jones

Removing time from SQL datetime

We use SQL server to store data—a lot. Sometimes we need to remove the time for a DATETIME column. This is useful for analysis or grouping by day. There is no built-in function that does this, so developers often write their own. I’ve written several, some of which are much faster than others.

Some people convert it to a VARCHAR without the time, and back to a DATETIME (effectively making the time midnight). Something like this:

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(15), GETDATE(), 101))

This works, but it’s slow when operating over tens of thousands of rows. It’s also a little tricky to remember if you don’t write SQL often.

If you use SQL 2008, you can use a new data type called DATE. It’s compatible with DATETIME, and it’s as simple as a CONVERT or CAST, meaning you can use operators on them.

SELECT CAST([Registered] AS DATE) FROM Users

You can cast it back to a DATETIME if you need the type to be a DATETIME. If your column doesn’t care about time and you use SQL 2008, I recommend changing the column type to DATE so you don’t have to worry about time.

However, if you use SQL 2005 or below, I recommend casting it to a FLOAT, calling FLOOR, then back to a DATETIME.

SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)

This is significantly faster than converting to the a VARCHAR and back to a DATETIME, mainly because when you cast a DATETIME to VARCHAR, SQL Server considers things like the collation, and what format it should use, then does it all again when parsing it back to a DATETIME.

When casting to a FLOAT, the integer part is the number of days, and the decimal part is the fraction part of the day, or time. Using FLOOR effectively gets rid of the decimal. You can also use CEILING to push it to the next day.

Kevin Jones is a Senior .NET Consultant and Team Lead at Thycotic Software, an agile software services and product development company based in Washington DC. Secret Server is our flagship password management software product.