MS SQL Date Only Without Time

Question

Question

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

Solution

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.

Answer 1

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.

Answer 2

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
    BETWEEN DATEADD(day, DATEDIFF(day, 0, @startDate), 0)
        AND DATEADD(ms,-2,DATEADD(day, 1, DATEDIFF(day, 0, @endDate)))

Be careful when dealing with date ranges because the SQL datetime data type is not accurate down the ms. Especially if you don't want entries that happen right at midnight to be counted twice.

Answer 3

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

Answer 4

that is very bad for performance, take a look at Only In A Database Can You Get 1000% + Improvement By Changing A Few Lines Of Code

functions on the left side of the operator are bad

here is what you need to do

declare @d datetime
select @d =  '2008-12-1 14:30:12'

where tstamp >= dateadd(dd, datediff(dd, 0, @d)+0, 0)
and tstamp < dateadd(dd, datediff(dd, 0, @d)+1, 0)

Run this to see what it does

select dateadd(dd, datediff(dd, 0, getdate())+1, 0)
select dateadd(dd, datediff(dd, 0, getdate())+0, 0)

Answer 5

If your using SQL Server 2008 it has this built in now, see this in books online

CAST(GETDATE() AS date)

Answer 6

WHERE DATEDIFF(day, tstamp, @dateParam) = 0

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.

Answer 7

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)

Answer 8

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.

Answer 9

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.

Answer 10

CONVERT(date, GETDATE()) and CONVERT(time, GETDATE()) work in sql server 2008. I'm uncertain about 2005.

Answer 11

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)