CODEWITHSUNDEEP
sqlsql-server
AnthonyTanjoco
AnthonyTanjoco

Reputation: 1

Please help me understand SQL quarter logic

I'm trying to understand the SQL logic in these statements...

Like in the first statement, I don't get how returning 478 (as of today) returns 2019-07-01 00:00:00.000...? Please help me understand the logic here.

SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) - 1, 0)-- First day of last quarter
SELECT DATEADD(dd, -1, DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0))-- Last day of last quarter

Upvotes: 0

Views: 58

Answers (2)

Kei
Kei

Reputation: 1026

I'm assuming you are using SQL Server. As per the docs, "qq" sets the unit to a quarter.

DATEDIFF(qq, 0, GETDATE()) gets the number of quarters since the 0th quarter, or Q1 of the year 1900.

As of now, we are in quarter 479. If you subtract 1, we get the previous quarter, 478. Quarter 478 starts on 2019-07-01.

As for your second query, SELECT DATEADD(dd, -1, DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)),

The portion below retrieves the current quarter. Since we are simply adding quarters to Q0, this gives us the first day of the current quarter.

DATEADD(qq, DATEDIFF(qq, 0, GETDATE())

Then, DATEADD(dd, -1... subtracts a day from that to get the last day of the previous quarter.

Upvotes: 0

SE1986
SE1986

Reputation: 2760

Let's break down what is happening in that first statement:

If we just run the inner DATEDIFF:

SELECT DATEDIFF(qq, 0, GETDATE())

this returns the number of quarters between 0 (1900-01-01) and todays date (19th November 2019), the answer being 479

Now to understand the outer DATEADD:

if we look at the syntax of DATEADD at BOL its:

DATEADD (datepart , number , date )

In your statement SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) - 1, 0)

the datepart is qq (quarter)

The number is DATEDIFF(qq, 0, GETDATE()) - 1 we know from above that the DATEDIFF equates to 479, then subtract 1 = 478

the date is 0 (1900-01-01)

So therefore your query is "add 478 quarters to 1900-01-01" which gives you the first date of that quarter: 2019-07-01

Upvotes: 3

Related Questions