Select statement MSSQL

Question

This is my function FirstDayInQtr

This is the sql query

SELECT @day =  DATEADD(qq, DATEDIFF(qq ,0, @InputDate),0)

who can explain this statement

I'm sure we could all explain it. What exactly is your question though? Is it giving output that you wouldn't expect? Have you tested it with some sample data to see what it does? — Rich Benner, Jul 05 '16 at 07:19
yes i tested it and it works perfectly. but i cant understand logic behind this statement — Sankalpa Nirmana, Jul 05 '16 at 07:21

score 1 · Answer 1 · edited May 23 '17 at 12:30

I believe this will clear your uncertainties: https://stackoverflow.com/a/3945396/6492765

Basically, SELECT is a T-SQL way of setting a value for a variable, the magic being in selecting from joins or other select concepts directly into variables. With assigning 1 value to only 1 variable, it does not differ from SET which, in your case, would be:

SET @day = DATEADD(qq, DATEDIFF(qq ,0, @InputDate),0)

score 0 · Answer 2 · answered Jul 05 '16 at 08:15

0

You assign the first day of the current quarter to variable @day

answered Jul 05 '16 at 08:15

Tyron78

4,117
2
17
32

score 0 · Accepted Answer · edited Nov 27 '16 at 16:13

The DATEADD() function adds or subtracts a specified time interval from a date.

Syntax : DATEADD(datepart,number,date)

qq = quarter,

yy = Year

in your question the date field is representative as 0 or -1 in the datepart syntax, SQL server relates 0 as 1900-01-01 00:00:00.000 and -1 as 1 day before 0 1899-12-31 00:00:00.000

so

select dateadd(qq,466,'1900-01-01 00:00:00.000') is the same as 


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

OR

SET @InsertDate = GETDATE()

SELECT   DATEADD(qq, DATEDIFF(qq ,0, @InsertDate),0)

Now, 466 is derived by the datediff(qq,0,GETDATE()) section of the function. According to calender year there are 466 quaters between 1900-01-01 00:00:00.000 and now.

so in @day u will get 2016-07-01 00:00:00.000, if your @InsertDate value is 2016-07-05 00:00:00.000.

Select statement MSSQL

3 Answers3