CODEWITHSUNDEEP
sqlsql-server
TanmoyDB
TanmoyDB

Reputation: 89

What is happening in this query?

I am trying to get the last of month, and in order to that i have written the following, to calculate the no. of days between today and the last date.

    select datediff(DAY,GETDATE(),dateadd(m,1,getdate()))-GETDATE()

the bold part gives me the no. of days between today and a month from today, say 30 or 31. and then I am subtracting today's date from 30 or 31, which is " -getdate() "

The output for the above query is

1786-06-06 11:44:30.540

Could you please explain what is happening in the query? I am not looking for a solution, I would like to know how is SQL-Server interpreting the query.

Thanks. :)

Upvotes: 1

Views: 141

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280645

The bold part of the expressions does not return a date, it returns a number of days:

31

Convert that to a datetime:

SELECT CONVERT(DATETIME, 31);

This is 31 days after day 0 (1900-01-01):

1900-02-01

Now, subtract GETDATE() as an integer (41512 days after day 0):

SELECT 31 - 41512 = -41481

Now add -41481 days to day 0:

SELECT DATEADD(DAY, -41481, 0);
-- or
SELECT DATEADD(DAY, -41481, '19000101');

Or:

SELECT CONVERT(DATETIME, 31 - CONVERT(INT, GETDATE()));

Now, I strongly recommend a couple of things:

  1. Don't use implicit date math. @date_var_or_col - 1 for example fails with new data types like DATE and DATETIME2.

  2. Don't use shorthand like m. If you mean MONTH, just take the massive productivity hit and type out MONTH. To see why, tell me if this provides the results you expect:

    SELECT DATEPART(y, GETDATE()), DATEPART(w, GETDATE());

Upvotes: 10

D Stanley
D Stanley

Reputation: 152644

I am subtracting today's date from 30 or 31, which is " -getdate() "

Sounds like you understand exactly what is happening, but maybe don't understand the results.

You are implicitly converting GETDATE() to a number, which represents the number of days (and fractional days) since 1/1/1900 12:00:00 AM

When you "subtract" GETDATE() (41,511 as of 8/27/2013) from 30 or 31 you get an answer of -41,480, or 41,480 days before 1/1/1900, which would be about 6/6/1786 (plus or minus a few hours for the fractional part).

Upvotes: 2

Related Questions