DATEDIFF(MONTH,0,getdate())

Question

DATEDIFF(MONTH,0,getdate())

Sony Francis @EY

SSCommitted

Points: 1539
More actions
May 8, 2012 at 5:59 am

#268394

Can you briefly explain the working of below query
select DATEDIFF(MONTH,0,getdate())
In my machine it returned as 1348

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113563 More actions · Answer 1

it is substituting 0 with the default date of '1900-01-01 00:00:00.000'

If only a time value is assigned to a variable of a date data type, the value of the missing date part is set to the default value: 1900-01-01. If only a date value is assigned to a variable of a time or date data type, the value of the missing time part is set to the default value: 00:00:00. If either startdate or enddate have only a time part and the other only a date part, the missing time and date parts are set to the default values.

As your not passing in a date or time for the starting datetime it is using its own default based on the MSDN quote above.

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

anthony.green (5/8/2012)
it is substituting 0 with the default date of '1900-01-01 00:00:00.000'

You can change the "zero" date at a server level, so test it before you count on it being "1900-01-01 00:00:00.000". Obviously this doesn't matter if it's being used for stripping parts off a datetime (e.g. get 1st of month etc), but may matter if it's being used in other cases.

--edit--

Can't find any reference to this so have removed. I'm sure I've read it somewhere, but it's possible that the only place I've seen it is when GSquared told me in 2011.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gazareth One Orange Chip Points: 27737 More actions · Answer 3

Cadavre (5/8/2012)
You can change the "zero" date at a server level

Can you?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Cadavre (5/8/2012)
anthony.green (5/8/2012)
it is substituting 0 with the default date of '1900-01-01 00:00:00.000'
You can change the "zero" date at a server level, so test it before you count on it being "1900-01-01 00:00:00.000". Obviously this doesn't matter if it's being used for stripping parts off a datetime (e.g. get 1st of month etc), but may matter if it's being used in other cases.

Would you please elaborate on this? I was not aware that you could change the '0' date.

Cadavre SSC-Forever Points: 41690 More actions · Answer 5

I'm sure I've read it somewhere, but to be honest after a quick search all I can find is this: -

GSquared (12/1/2011)
You can actually change the zero date at a server-level, so test it before you count on that. Won't matter if it's being used to strip parts off of dates (like midnight calculations or 1st of month calculations), but might matter if it's being used for something else.[/url]

So I may have to strike out my previous comment.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gazareth One Orange Chip Points: 27737 More actions · Answer 6

Cadavre (5/8/2012)
I'm sure I've read it somewhere, but to be honest after a quick search all I can find is this: -
GSquared (12/1/2011)
You can actually change the zero date at a server-level, so test it before you count on that. Won't matter if it's being used to strip parts off of dates (like midnight calculations or 1st of month calculations), but might matter if it's being used for something else.[/url]
So I may have to strike out my previous comment.

Then we must get GSquared to elaborate on his comment! 😀

drew.allen SSC Guru Points: 77013 More actions · Answer 7

anthony.green (5/8/2012)
it is substituting 0 with the default date of '1900-01-01 00:00:00.000'
If only a time value is assigned to a variable of a date data type, the value of the missing date part is set to the default value: 1900-01-01. If only a date value is assigned to a variable of a time or date data type, the value of the missing time part is set to the default value: 00:00:00. If either startdate or enddate have only a time part and the other only a date part, the missing time and date parts are set to the default values.
As your not passing in a date or time for the starting datetime it is using its own default based on the MSDN quote above.

Actually, that's not true. You are passing in a date expressed as an integer. You can see this by running the following:

SELECT DATEDIFF(DAY, 7, '1900-01-08')

If what you said were true, it would substitute the default date and time for the 7 and produce a result of 7, whereas the actual result is 0. It's debatable whether you are also specifying a time, although clearly 0.0 would also specify a time of 00:00:00.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA