Fun with DateDiff 1

[ptheriault]

Without running it.
What do you think this will return..

[COLOR=green]-- Prepare sample data
[/color][COLOR=blue]DECLARE[/color]    @Dates [COLOR=blue]TABLE[/color] (StartingDate [COLOR=#FF00FF]DATETIME[/color], EndingDate [COLOR=#FF00FF]DATETIME[/color])

[COLOR=blue]INSERT[/color]     @Dates
[COLOR=blue]SELECT[/color]     [COLOR=red]'20010101'[/color], [COLOR=red]'20070204'[/color] UNION ALL
[COLOR=blue]SELECT[/color]     [COLOR=red]'20071231'[/color], [COLOR=red]'20080101'[/color] UNION ALL
[COLOR=blue]SELECT[/color]     [COLOR=red]'20070318'[/color], [COLOR=red]'20080101'[/color]

[COLOR=green]-- Show the expected output
[/color]
[COLOR=blue]SELECT[/color]     StartingDate,
           EndingDate,
           [COLOR=#FF00FF]DATEDIFF[/color]([COLOR=#FF00FF]MONTH[/color], StartingDate, EndingDate) [COLOR=blue]AS[/color] [[COLOR=#FF00FF]Month[/color] [COLOR=#FF00FF]DatePart[/color]],
           [COLOR=#FF00FF]DATEDIFF[/color]([COLOR=#FF00FF]YEAR[/color], StartingDate, EndingDate) [COLOR=blue]AS[/color] [[COLOR=#FF00FF]Year[/color] [COLOR=#FF00FF]DatePart[/color]]
[COLOR=blue]FROM[/color]       @Dates
[COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color]   StartingDate

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[SQLDenis]

That is normal behaviour, check this out
Notice 1 day difference between the dates

SELECT DATEDIFF(YEAR, '20071231', '20080101') AS YEARDiff ,
DATEDIFF(QUARTER, '20071231', '20080101') AS QUARTERDiff,
DATEDIFF(MONTH, '20071231', '20080101') AS MONTHDiff,
DATEDIFF(DAY, '20071231', '20080101') AS DAYDiff

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[ptheriault]

I knew you would get it. But in reality shouldn't your query return, 0, 0, 0, 1?

At least that is what you would think it should return.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

to get the expected results should we write something like this?

Spoiler

SELECT CASE
WHEN DATEPART(day, '20071231') > DATEPART(day, '20080101') THEN DATEDIFF(month, '20071231', '20080101') - 1
ELSE DATEDIFF(month, '20071231', '20080101')
END as a

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[SQLDenis]

Take a look here:

http://www.sqlmag.com/Articles/ArticleID/48764/pg/2/2.html

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[gmmastros]

Paul,

The DateDiff function returns the number of transitions.

Ex: Day transisitons from 1 day to another at midnight.

[COLOR=blue]Select[/color] [COLOR=#FF00FF]DateDiff[/color]([COLOR=#FF00FF]Day[/color], [COLOR=red]'20070423 23:59:59'[/color], [COLOR=red]'20070424'[/color])

Similarly, when the month changes (even if it's only a second difference), DateDiff will return 1 (for month).

[COLOR=blue]Select[/color] [COLOR=#FF00FF]DateDiff[/color]([COLOR=#FF00FF]Month[/color], [COLOR=red]'20070331 23:59:59'[/color], [COLOR=red]'20070401'[/color])

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[monksnake]

Paul, you need to add "Teaser" in your thread title, I almost didn't look and get teased.

<.

 

[gmmastros]

Ok. Here's another one... What is the output?

[COLOR=blue]Declare[/color] @D1 [COLOR=#FF00FF]DateTime[/color]
[COLOR=blue]Declare[/color] @D2 [COLOR=#FF00FF]DateTime[/color]

[COLOR=blue]Set[/color] @D1 = [COLOR=red]'10:20:15:30'[/color]
[COLOR=blue]Set[/color] @D2 = [COLOR=red]'10:20:15.30'[/color]

[COLOR=blue]Select[/color] [COLOR=red]'Dates Are Equal'[/color]
[COLOR=blue]Where[/color]  @D1 = @D2

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

you got me there.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.: ;-)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[monksnake]

I'm assuming

Spoiler

Dates Are Equal (cause it compares with the day, since no day was given in each example, they default to the same date)

<.

 

[gmmastros]

monksnake,

Your assumption is wrong. If you don't specify a day, Jan 1, 1900 will be used. Comparisons are down to the millisecond.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[earthandfire]

I got caught by that a couple of months ago (through mis-typing)

Spoiler

Not equal - The first will be 10:20:15:030, whereas the second will (I think) 10:20:15:300, and if you had 15.03, that would be 15:030 and 15:003 would be 15:003 - or I have I got those backwards?

 

[earthandfire]

On reflection, I think my previous post is wrong - I have got it backwards.

 

[gmmastros]

Spoiler

If you use a colon to seperate the seconds from the milliseconds, SQL Server assumes the number is in thousands of a second.

Declare @D1 DateTime
Declare @D2 DateTime

Set @D1 = '10:20:15:300'
Set @D2 = '10:20:15.300'

Select 'Dates Are Equal'
Where @D1 = @D2

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[monksnake]

I'm like 0/10 on teasers.

<.