CODEWITHSUNDEEP
sql-servert-sql
BSchnitzel
BSchnitzel

Reputation: 158

What is the result of this statement?

I am working through a prepared statement and ran into an interesting bit of date manipulation code. It is critical that I am understanding it correctly since I am doing a rewrite.

I googled the code directly and found something similar on SO, but it was not an exact match of the code (I know, it never is :-) I just need verification that I am interpreting it as only returning minutes seconds for the next day?

DECLARE @dEnd DATE = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dIncr)+1,0))

I am putting it in a conditional statement that is comparing it to the following:

DECLARE @dIncr DATE = CAST(@Month as varchar(2)) + '/01/' + CAST(@Year as varchar(4))

Edit Then the variables are ran using a cursor in this conditional statement:

WHILE ( @dIncr <= @dEnd )
BEGIN
--Random code
END

Edit 2 SQL Server v:

Microsoft SQL Azure (RTM) - 12.0.2000.8 
Nov 20 2018 21:01:54 
Copyright (C) 2018 Microsoft Corporation

Thanks ahead of time for any help out there!

Upvotes: 1

Views: 93

Answers (2)

JNevill
JNevill

Reputation: 50119

 DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dIncr)+1,0))

This gets the last day of the current month for the date @dIncr

Breaking this down:

DATEDIFF(m,0,@dIncr)+1

Says: Return the number of months + 1 for the date in the variable @dIncr since the sql date epoch of January 1 1900 (This is the 0 in the function)

DATEADD(mm, DATEDIFF(m,0,@dIncr)+1,0)

Says: Return the date represented by adding that previously calculated number of months to the sql date epoch January 1 1900

DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dIncr)+1,0))

Says: Subtract a second from that date.

Upvotes: 1

Richard Hansell
Richard Hansell

Reputation: 5403

That's a "trick" to determine the last day of the month that your first date falls in. If you run it like this it becomes easier to follow:

DECLARE @dIncr DATE = '20170101';
DECLARE @dEnd DATE = DATEADD(SECOND, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dIncr) + 1, 0));
SELECT @dEnd;

Which will give an answer of 31-01-2017.

Upvotes: 1

Related Questions