Hit me on the head - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Hit me on the head

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-08 : 14:54:56

I know I've seen this here.

I also know I'm lazy.

I did look in BOL (yawn). Datepart, datediff, I can't get a date...

I can do what I want but it's messy with those functions. There's a quick easy way to get this done.

How can I strip the time element from a datetime variable so it's midnight for the purposes of comparing?

@MyDate DATETIME

I need

WHERE CreateDate <= @MyDate -- This needs to include all entries that fall on this date (@MyDate). Gotta round it to the next day at midnight.

Come on. Where's Arnold when you need him?

Sam

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-07-08 : 15:04:04

How about just (convert(varchar(10),@MyDate,101))

Jim
Users <> Logic

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-08 : 15:26:56

You want where DATEDIFF(dd, ..., ...) <= 0.

Jonathan
{0}

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-08 : 15:31:13

Hi Jim,

Thanks. Based on your feedback, here's my shortcut to round to next day midnight

WHERE CreateDate <= cast(cast(getdate()+1 as varchar(12)) as datetime)

Does GETDATE()+1 reliably add a day without resorting to the DATEADD function?

Sam

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-08 : 15:38:54

Thanks Jonathan,

@MyDate is entered on an ASP form. Today's date 'Jul 9, 2003' wouldn't match current entries. I need to round to tomorrow midnight (I think).

But

WHERE DATEDIFF(DD, CreateDate, CAST(@MyDate + 1 as varchar(12)) <= 0

Should work.

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-08 : 15:55:43

You lost me there. Today's the 8th, and datediff( dd, '2003-07-08 00:00:00', '2003-07-08 23:59:59') does return zero. Why add a day?

Jonathan
{0}

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-08 : 16:07:11

Maybe you are right?

I didn't realize

WHERE DATEDIFF (DD, CreateDate, 'April 14, 2003') <=0

Would return all days earlier or equal to April 14, including all rows with times greater than zero on April 14.

I was stuck on the difference becoming negative if CreateDate was

DATEDIFF(DD, 'April 14, 2003 1PM', 'April 14, 2003') <= 0

I hadn't realized the above statement would be true.

Sam

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-08 : 16:09:56

The reason this works is you're specifying days to DATEDIFF(), which returns an integer w/ the # of date boundaries crossed. So unless one of the dates is 24 hours or more away from the other, you will cross zero day boundaries.

Jonathan
{0}

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-08 : 16:22:55

I ran a test just now, and DATEDIFF completely ignores the time part. 24 hours difference doesn't matter. It returns differences in the date part only, which is just what I wanted.

Thanks,

Sam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-08 : 17:07:26

Never use criteria like:

DATEDIFF(dd, ..., ...) <= 0

That cannot make use of any possible indexes on your data.

WHERE CreateDate < DateAdd(dd,datediff(dd,@MyDate,0)+1,0)

will be much more efficient.

- Jeff

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-08 : 17:11:33

Dr. Cross Join,

I was going to say never say never without saying why.

But I get your point. Are you sure that indicies are used using DATEADD...?

Sam

- and never follow your own advice.

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-08 : 17:18:46

quote:
24 hours difference doesn't matter

Yep, misspoke myself. It's just the date/time boundary crossing that's important, which for a day is midnight. That's not to say it ignores the time; it all depends on whether you've asked for a date interval greater than or less than a day.

Jonathan
{0}

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-08 : 17:22:34

DATEDIFF(dd, ..., ...) <= 0

must be evaluated for every row in your table.

WHERE CreateDate < DateAdd(dd,datediff(dd,@MyDate,0)+1,0)

is turned into:

WHERE CreateDate < SomeDate

before the query is executed, because the expression on the right is a constant and not dependant on rows in your result set ... Thus, if you have an index on CreateDate, only the exact rows you are requesting are returned.

- Jeff

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-08 : 17:30:45

Graz should rename your title to "The Doctor"

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-08 : 17:43:49

*Ahem*

Dr., It doesn't work.

print dateadd(dd, datediff(dd, getdate(), 0),0)

gives

Jun 26 1796 12:00AM

Shouldn't the date match GETDATE()?

Sam

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-08 : 17:49:46

you've got the parameters to datediff() backwards ...

Jonathan
{0}

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-08 : 17:51:21

quote:

DATEDIFF(dd, ..., ...) <= 0
must be evaluated for every row in your table.
WHERE CreateDate < DateAdd(dd,datediff(dd,@MyDate,0)+1,0)
is turned into:
WHERE CreateDate < SomeDate

This is precisely what the optimizer should do ... (hint to Microsoft SQL Server optimization team developers)

Jonathan
{0}

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-08 : 17:52:08

Thanks.

Sam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-08 : 18:32:58

ooops...sorry about that Sam ...

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources