CODEWITHSUNDEEP
sqlsql-servert-sql
regex
regex

Reputation: 3601

MS SQL Date Only Without Time

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.

Upvotes: 49

Views: 158082

Answers (12)

JoshBerke
JoshBerke

Reputation: 67068

If you're using SQL Server 2008 it has this built in now, see this in books online

CAST(GETDATE() AS date)

Upvotes: 101

Benjamin Autin
Benjamin Autin

Reputation: 4171

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.

Upvotes: -1

Booji Boy
Booji Boy

Reputation: 4582

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.

Upvotes: 0

AaronS
AaronS

Reputation: 435

CONVERT(date, GETDATE()) and CONVERT(time, GETDATE()) works in SQL Server 2008. I'm uncertain about 2005.

Upvotes: 10

Mark Brackett
Mark Brackett

Reputation: 85635

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.

Upvotes: 1

Sameer
Sameer

Reputation: 61

How about this?

SELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)

Upvotes: 6

Rob Boek
Rob Boek

Reputation: 1973

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))

Upvotes: 0

Xander
Xander

Reputation: 21

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)

Upvotes: 0

Sam Saffron
Sam Saffron

Reputation: 131112

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.

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 415630

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)

Upvotes: 11

SQLMenace
SQLMenace

Reputation: 134941

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)

Upvotes: 40

Lusid
Lusid

Reputation: 4528

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

Upvotes: 1

Related Questions