Hello All,
I've had some confusion for quite some time with essentially flooring a DateTime SQL type using T-SQL. Essentially, I want to take a DateTime value of say 2008-12-1 14:30:12 and make it 2008-12-1 00:00:00. Alot of the queries we run for reports use a date value in the WHERE clause, but I either have a start and end date value of a day and use a BETWEEN, or I find some other method.
Currently I'm using the following:
WHERE CAST(CONVERT(VARCHAR, [tstamp], 102) AS DATETIME) = @dateParam
However, this seems kinda clunky. I was hoping there would be something more simple like
CAST([tstamp] AS DATE)
Some places online recommend using DATEPART() function, but then I end up with something like this:
WHERE DATEPART(year, [tstamp]) = DATEPART(year, @dateParam)
AND DATEPART(month, [tstamp]) = DATEPART(month, @dateParam)
AND DATEPART(day, [tstamp]) = DATEPART(day, @dateParam)
Maybe I'm being overly concerned with something small and if so please let me know. I just want to make sure the stuff I'm writing is as efficient as possible. I want to eliminate any weak links.
Any suggestions?
Thanks,
C
Thanks everyone for the great feedback. A lot of useful information. I'm going to change around our functions to eliminate the function on the left hand side of the operator. Although most of our date columns don't use indexes, it is probably still a better practice.
This question is related to
sql
sql-server
tsql
Here's a query that will return all results within a range of days.
DECLARE @startDate DATETIME
DECLARE @endDate DATETIME
SET @startDate = DATEADD(day, -30, GETDATE())
SET @endDate = GETDATE()
SELECT *
FROM table
WHERE dateColumn >= DATEADD(day, DATEDIFF(day, 0, @startDate), 0)
AND dateColumn < DATEADD(day, 1, DATEDIFF(day, 0, @endDate))
Alternatively you could use
declare @d datetimeselect
@d = '2008-12-1 14:30:12'
where tstamp
BETWEEN dateadd(dd, datediff(dd, 0, @d)+0, 0)
AND dateadd(dd, datediff(dd, 0, @d)+1, 0)
DATEADD(d, 0, DATEDIFF(d, 0, [tstamp]))
Edit: While this will remove the time portion of your datetime, it will also make the condition non SARGable. If that's important for this query, an indexed view or a between clause is more appropriate.
Careful here, if you use anything a long the lines of WHERE CAST(CONVERT(VARCHAR, [tstamp], 102) AS DATETIME) = @dateParam it will force a scan on the table and no indexes will be used for that portion.
A much cleaner way of doing this is defining a calculated column
create table #t (
d datetime,
d2 as
cast (datepart(year,d) as varchar(4)) + '-' +
right('0' + cast (datepart(month,d) as varchar(2)),2) + '-' +
right('0' + cast (datepart(day,d) as varchar(2)),2)
)
-- notice a lot of care need to be taken to ensure the format is comparable. (zero padding)
insert #t
values (getdate())
create index idx on #t(d2)
select d2, count(d2) from #t
where d2 between '2008-01-01' and '2009-01-22'
group by d2
-- index seek is used
This way you can directly check the d2 column and an index will be used and you dont have to muck around with conversions.
If you're using SQL Server 2008 it has this built in now, see this in books online
CAST(GETDATE() AS date)
WHERE DATEDIFF(day, tstamp, @dateParam) = 0
This should get you there if you don't care about time.
This is to answer the meta question of comparing the dates of two values when you don't care about the time.
The Date functions posted by others are the most correct way to handle this.
However, it's funny you mention the term "floor", because there's a little hack that will run somewhat faster:
CAST(FLOOR(CAST(@dateParam AS float)) AS DateTime)
Yes, T-SQL can feel extremely primitive at times, and it is things like these that often times push me to doing a lot of my logic in my language of choice (such as C#).
However, when you absolutely need to do some of these things in SQL for performance reasons, then your best bet is to create functions to house these "algorithms."
Take a look at this article. He offers up quite a few handy SQL functions along these lines that I think will help you.
http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx
CONVERT(date, GETDATE()) and CONVERT(time, GETDATE()) works in SQL Server 2008. I'm uncertain about 2005.
If you're using SQL Server 2008 it has this built in now, see this in books online
CAST(GETDATE() AS date)
FWIW, I've been doing the same thing as you for years
CAST(CONVERT(VARCHAR, [tstamp], 102) AS DATETIME) = @dateParam
Seems to me like this is one of the better ways to strip off time in terms of flexibility, speed and readabily. (sorry). Some UDF functions as suggested can be useful, but UDFs can be slow with larger result sets.
Careful here, if you use anything a long the lines of WHERE CAST(CONVERT(VARCHAR, [tstamp], 102) AS DATETIME) = @dateParam it will force a scan on the table and no indexes will be used for that portion.
A much cleaner way of doing this is defining a calculated column
create table #t (
d datetime,
d2 as
cast (datepart(year,d) as varchar(4)) + '-' +
right('0' + cast (datepart(month,d) as varchar(2)),2) + '-' +
right('0' + cast (datepart(day,d) as varchar(2)),2)
)
-- notice a lot of care need to be taken to ensure the format is comparable. (zero padding)
insert #t
values (getdate())
create index idx on #t(d2)
select d2, count(d2) from #t
where d2 between '2008-01-01' and '2009-01-22'
group by d2
-- index seek is used
This way you can directly check the d2 column and an index will be used and you dont have to muck around with conversions.
DATEADD(d, 0, DATEDIFF(d, 0, [tstamp]))
Edit: While this will remove the time portion of your datetime, it will also make the condition non SARGable. If that's important for this query, an indexed view or a between clause is more appropriate.
Yes, T-SQL can feel extremely primitive at times, and it is things like these that often times push me to doing a lot of my logic in my language of choice (such as C#).
However, when you absolutely need to do some of these things in SQL for performance reasons, then your best bet is to create functions to house these "algorithms."
Take a look at this article. He offers up quite a few handy SQL functions along these lines that I think will help you.
http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx
Alternatively you could use
declare @d datetimeselect
@d = '2008-12-1 14:30:12'
where tstamp
BETWEEN dateadd(dd, datediff(dd, 0, @d)+0, 0)
AND dateadd(dd, datediff(dd, 0, @d)+1, 0)
WHERE DATEDIFF(day, tstamp, @dateParam) = 0
This should get you there if you don't care about time.
This is to answer the meta question of comparing the dates of two values when you don't care about the time.
The Date functions posted by others are the most correct way to handle this.
However, it's funny you mention the term "floor", because there's a little hack that will run somewhat faster:
CAST(FLOOR(CAST(@dateParam AS float)) AS DateTime)
If you're using SQL Server 2008 it has this built in now, see this in books online
CAST(GETDATE() AS date)
Here's a query that will return all results within a range of days.
DECLARE @startDate DATETIME
DECLARE @endDate DATETIME
SET @startDate = DATEADD(day, -30, GETDATE())
SET @endDate = GETDATE()
SELECT *
FROM table
WHERE dateColumn >= DATEADD(day, DATEDIFF(day, 0, @startDate), 0)
AND dateColumn < DATEADD(day, 1, DATEDIFF(day, 0, @endDate))
How about this?
SELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)
WHERE DATEDIFF(day, tstamp, @dateParam) = 0
This should get you there if you don't care about time.
This is to answer the meta question of comparing the dates of two values when you don't care about the time.
If you're using SQL Server 2008 it has this built in now, see this in books online
CAST(GETDATE() AS date)
Yes, T-SQL can feel extremely primitive at times, and it is things like these that often times push me to doing a lot of my logic in my language of choice (such as C#).
However, when you absolutely need to do some of these things in SQL for performance reasons, then your best bet is to create functions to house these "algorithms."
Take a look at this article. He offers up quite a few handy SQL functions along these lines that I think will help you.
http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx
The Date functions posted by others are the most correct way to handle this.
However, it's funny you mention the term "floor", because there's a little hack that will run somewhat faster:
CAST(FLOOR(CAST(@dateParam AS float)) AS DateTime)
FWIW, I've been doing the same thing as you for years
CAST(CONVERT(VARCHAR, [tstamp], 102) AS DATETIME) = @dateParam
Seems to me like this is one of the better ways to strip off time in terms of flexibility, speed and readabily. (sorry). Some UDF functions as suggested can be useful, but UDFs can be slow with larger result sets.
DATEADD(d, 0, DATEDIFF(d, 0, [tstamp]))
Edit: While this will remove the time portion of your datetime, it will also make the condition non SARGable. If that's important for this query, an indexed view or a between clause is more appropriate.
CONVERT(date, GETDATE()) and CONVERT(time, GETDATE()) works in SQL Server 2008. I'm uncertain about 2005.
WHERE DATEDIFF(day, tstamp, @dateParam) = 0
This should get you there if you don't care about time.
This is to answer the meta question of comparing the dates of two values when you don't care about the time.
FWIW, I've been doing the same thing as you for years
CAST(CONVERT(VARCHAR, [tstamp], 102) AS DATETIME) = @dateParam
Seems to me like this is one of the better ways to strip off time in terms of flexibility, speed and readabily. (sorry). Some UDF functions as suggested can be useful, but UDFs can be slow with larger result sets.
Careful here, if you use anything a long the lines of WHERE CAST(CONVERT(VARCHAR, [tstamp], 102) AS DATETIME) = @dateParam it will force a scan on the table and no indexes will be used for that portion.
A much cleaner way of doing this is defining a calculated column
create table #t (
d datetime,
d2 as
cast (datepart(year,d) as varchar(4)) + '-' +
right('0' + cast (datepart(month,d) as varchar(2)),2) + '-' +
right('0' + cast (datepart(day,d) as varchar(2)),2)
)
-- notice a lot of care need to be taken to ensure the format is comparable. (zero padding)
insert #t
values (getdate())
create index idx on #t(d2)
select d2, count(d2) from #t
where d2 between '2008-01-01' and '2009-01-22'
group by d2
-- index seek is used
This way you can directly check the d2 column and an index will be used and you dont have to muck around with conversions.
Here's a query that will return all results within a range of days.
DECLARE @startDate DATETIME
DECLARE @endDate DATETIME
SET @startDate = DATEADD(day, -30, GETDATE())
SET @endDate = GETDATE()
SELECT *
FROM table
WHERE dateColumn >= DATEADD(day, DATEDIFF(day, 0, @startDate), 0)
AND dateColumn < DATEADD(day, 1, DATEDIFF(day, 0, @endDate))
DATEADD(d, 0, DATEDIFF(d, 0, [tstamp]))
Edit: While this will remove the time portion of your datetime, it will also make the condition non SARGable. If that's important for this query, an indexed view or a between clause is more appropriate.
The Date functions posted by others are the most correct way to handle this.
However, it's funny you mention the term "floor", because there's a little hack that will run somewhat faster:
CAST(FLOOR(CAST(@dateParam AS float)) AS DateTime)
How about this?
SELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)
FWIW, I've been doing the same thing as you for years
CAST(CONVERT(VARCHAR, [tstamp], 102) AS DATETIME) = @dateParam
Seems to me like this is one of the better ways to strip off time in terms of flexibility, speed and readabily. (sorry). Some UDF functions as suggested can be useful, but UDFs can be slow with larger result sets.
Yes, T-SQL can feel extremely primitive at times, and it is things like these that often times push me to doing a lot of my logic in my language of choice (such as C#).
However, when you absolutely need to do some of these things in SQL for performance reasons, then your best bet is to create functions to house these "algorithms."
Take a look at this article. He offers up quite a few handy SQL functions along these lines that I think will help you.
http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx
Source: Stackoverflow.com