DateTime coolness - something new, surprisingly 2

[ESquared]

Most of us have seen, among many ways to chop the time off a datetime:

SELECT DateAdd(dd, DateDiff(dd, 0, GetDate())

But you know what occurred to me? If the value is already going to be cast as datetime, using an integer data type works because it gets promoted. Which means the DateAdd is completely redundant. The dateadd is adding 0 to an integer to get a date which already is that integer, because you're adding 0! Look:

DECLARE @DateDemo TABLE (TheDate datetime NOT NULL)
INSERT @DateDemo SELECT DateDiff(dd, 0, GetDate())
SELECT * From @DateDemo

-- and if you absolutely must have a datetime value, then:

SELECT Convert(datetime, DateDiff(dd, 0, GetDate()))

-- which to me is better because convert is likely a less CPU intensive function... maybe testing is in order. There is at least one less operand. And it may be more clear to a programmer's eyes what is going on in there.

Getting the time is just as simple, only you don't ever need the convert:

SELECT GetDate() - DateDiff(dd, 0, GetDate())

Isn't that cool?

And checking to see that a date has no time portion, or no date portion, just got easier, too:

SELECT CASE WHEN GetDate() = DateDiff(dd, 0, GetDate()) THEN '12:00:00 exactly' ELSE 'some time value' END

SELECT CASE WHEN 0 = DateDiff(dd, 0, GetDate()) THEN 'Date Portion = 0' ELSE 'some date value' END

[COLOR=black #d0d0d0]When I walk, I sometimes bump into things. I am closing my eyes so that the room will be empty.[/color]

 

[AlexCuse]

You missed a bit in your first example:

SELECT DateAdd(dd, DateDiff(dd, 0, GetDate())[b], 0)[/b]

I kind of think that with all the work that has been put into optimizing date arithmetic in SQL Server, using dateadd is probably at least as efficient as casting the integer to a datetime value. You may be right though. If you do any testing I would be curious to see the results.

Alex

Ignorance of certain subjects is a great part of wisdom

 

[ESquared]

Yes, thank you for correcting me, Alex. I'm surprised my brain was functioning last night at all! Sometimes things come to use in different moods that don't come in other moods...

But one of my points was that even if it's just as efficient, having one less DateAdd in there is clearer to me. A convert is easy to understand at a glance than a DateAdd.

I have a table at work called AllDay which has every possible datetime value for one whole day (yes, it has 25,920,000 rows). I use it whenever I need some mondo datetime testing or date conversion method validation. I'll test it out a bit when I get a chance.

[COLOR=black #d0d0d0]When I walk, I sometimes bump into things. I am closing my eyes so that the room will be empty.[/color]

 

[AlexCuse]

Eh, I knew what you meant. I would be very interested to know what you come up with. I don't use this very often at all, but I know the question comes up on here a lot...

Ignorance of certain subjects is a great part of wisdom

 

[ESquared]

Against my allday table with 25,920,000 rows in it (all possible times for one day). First, date only validation:

--Query 1:
select Convert(datetime, DateDiff(dd, 0, tm)) from allday where Convert(datetime, DateDiff(dd, 0, tm)) <> '9999-12-31'

--Query 2:
select DateAdd(dd, DateDiff(dd, 0, tm), 0) from allday where DateAdd(dd, DateDiff(dd, 0, tm), 0) <> '9999-12-31'

Both return an empty resultset, indicating they both work correctly.

Performance, average of 20 runs:

[tt] CPU Reads Duration
Query 1 4570 68433 8012
Query 2 8844 68429 10744[/tt]

Now for the time only validation:

--Query 3:
select tm - DateDiff(dd, 0, tm) from allday where tm - DateDiff(dd, 0, tm) + '9999-12-31' <> tm

--Query 4:
select tm - DateAdd(dd, DateDiff(dd, 0, tm), 0) from allday where tm - DateAdd(dd, DateDiff(dd, 0, tm), 0) + '9999-12-31' <> tm

I decided to throw in a Convert on query 4, (making query 5) to see if it was more efficient, but it simply performed worse than query 4 for all parameters.

--Query 5:
select tm - Convert(datetime, DateDiff(dd, 0, tm)) from allday where tm - Convert(datetime, DateDiff(dd, 0, tm)) + '9999-12-31' <> tm

Again, empty resultsets proved formulas were working correctly. Average of 20 runs:

[tt] CPU Reads Duration
Query 3 11523 68441 12890
Query 4 10685 68441 12281
Query 5 11488 68445 12707[/tt]

The CPU and Duration numbers vary a fair amount between different runs for both sets of queries. For query 1 and 2, we see that the convert() method was always faster/less expensive, by quite a bit, and the difference in reads was negligible. For query 3 and 4, the DateAdd method was always a little faster/less expensive, but by a much smaller margin.

To my surprise, the reads were usually different between the query pairs. There was less variation in the reads than in the other numbers. Why there was any variation at all I am not sure, probably a product of the table being huge and how much work could be done on each read based on CPU and memory, with perhaps an extra read kicking in once in a while.

Overall, I'm sticking with my new way that does not use an extra DateAdd.

[COLOR=black #d0d0d0]When I walk, I sometimes bump into things. I am closing my eyes so that the room will be empty.[/color]

 

[ESquared]

Correction:

but it simply performed worse than query 4 for all parameters.

Er, that was true when I was only running it one time. Once I switched to the average of 20, it performed very slightly and probably insignificantly better. The numbers are close enough that I suspect they in fact represent identical work.

[COLOR=black #d0d0d0]When I walk, I sometimes bump into things. I am closing my eyes so that the room will be empty.[/color]

 

[AlexCuse]

That is pretty cool. Be prepared to correct me for a few weeks of 'how to remove time component from date/time column' questions (whenever they start up again ;-) )

Thanks for sharing Just goes to show, there could always be something new to learn (even when you think you've beaten the subject to death!)

Alex

Ignorance of certain subjects is a great part of wisdom

 

[ESquared]

You just have to explain when to add the Convert() or not. For example you don't need it when inserting to a date datatype column. Or I suppose you could always add that to avoid confusion with people getting integer columns.

[COLOR=black #d0d0d0]When I walk, I sometimes bump into things. I am closing my eyes so that the room will be empty.[/color]

 

[ESquared]

Mistakes abound...

I decided to throw in a Convert on query 4,

That should be on query [red]3[/red]...

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]

 

[unclejimbob]

Thanks, this also relates to a question I posed back in May about why I should be using dateadd when I don't need
to return the time component. I will add your observations to my grab-bag of cool tricks - and this therefore deserves a purple thingy.