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.
[...] Removing time from SQL datetime (Kevin Jones) [...]
I believe keeping it as datetime is even faster. I remember doing performance tests on huge loops but I don’t recall if I ever published the results.
SELECT DATEADD(DAY, 0, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP));
Of course if you are assigning the value to a column or a DATETIME/SMALLDATETIME variable, you can avoid one operation:
DECLARE @d SMALLDATETIME;
SET @d = DATEDIFF(DAY, 0, CURRENT_TIMESTAMP);
SELECT @d;
Hi Aaron,
Thanks for the excellent tip! However, I did a little bit of performance testing myself and found the cast to FLOAT is just ever so slightly faster — most of the time
Using the DATEDIFF operation over about 2,000,000 rows took 00:00:12.626. Using the FLOAT operation is 00:00:12.601. This was an average of 10 times.
In some circumstances your operation was faster.
One other thing to note is I am using a 64-bit SKU – some operations (like float arithmetic) can be faster on x64.
Here was the basic test script:
CREATE TABLE ##Test
(
Foo DATETIME
)
GO
DECLARE @tracker INT
SET @tracker = 0
WHILE(@tracker < 2000000)
BEGIN
INSERT INTO ##Test SELECT GETDATE()
SET @tracker = @tracker + 1
END
GO
SELECT DATEADD(DAY, 1, DATEDIFF(DAY, 0, Foo)) FROM ##Test
SELECT CAST(FLOOR(CAST(Foo AS FLOAT)) AS DATETIME) FROM ##Test
DROP TABLE ##Test
Here’s a challenge:
Can anyone come up with a set based way to remove the DATE and keep only the TIME that will work with SQL 2005?
There is more powerful way to round the datetime to any period.
declare @a datetime
set @a = getdate()
select
@a [full],
dateadd(minute, datediff(minute, ”, @a), ”) [minute],
dateadd(day, datediff(day, ”, @a), ”) [day],
dateadd(month, datediff(month, ”, @a), ”) [month],
dateadd(year, datediff(year, ”, @a), ”) [year]
sir,
i saved all my dates in MM/dd/yyyy in ms-access database. in front end i show in dd/MM/yyyy format.
For my update query i am taking date from different windows froms which is like
#13/07/2009# but at database level it will it is #13/07/2009 1:12:09 AM#
so it compare date as well as time . but i don’t want compare my date with time also…
so what i do ?
it is for Ms-acess…