Home 2017 October SQL Puzzle: SQL Advance Query – Find number the first day of Quarter of Year

SQL Puzzle: SQL Advance Query – Find number the first day of Quarter of Year

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Check the below input date and expected output to find the first day of Quarter of year

Input Date Date:

Expected output:

Solution:

Please try the different solution for this puzzle and share it via comment...

Anvesh Patel
SQL Puzzle: SQL Advance Query - Solve Date Overlapping ProblemSQL Puzzle: SQL Advance Query - Find number of days of the Month of any date
Comments: 3
  1. Dinesh.IS
    October 12, 2017 at 6:51 am

    Declare @InPutDate DateTime=’2017/09/08′
    ;
    WITH CTE
    (Monthid,QuaterMonth)
    As
    (Select 1 As Monthid,0 As Quater
    Union All
    Select Monthid+1,Monthid%3
    From CTE Where Monthid+1<=12),
    N
    As
    (Select Convert(DateTime,Convert(VarChar,Year(@InPutDate))+'/'+Cast(Monthid As VarChar(2))+'/'+Cast('1' As VarChar(2))) As QuaterMonth From CTE Where QuaterMonth=0
    )
    Select Max(QuaterMonth) As QuaterDay From N Where QuaterMonth<=@InPutDate

    ReplyCancel
  2. Dinesh.IS
    October 12, 2017 at 6:58 am

    –Method-2
    Declare @InPutDate DateTime=’2017/09/08′
    ;
    WITH CTE
    As
    (Select (Convert(DateTime,Convert(VarChar(4),Year(@InputDate))+’/’+Cast(Monthid As VarChar(2))+’/’+Cast(‘1’ As VarChar(4)))) QuaterDay
    From (Select 1 As Monthid
    Union All
    Select 4 As Monthid
    Union All
    Select 7 As Monthid
    Union All
    Select 10 As Monthid) A)
    Select Max(QuaterDay) As QuaterDay From CTE Where QuaterDay<=@InputDate

    ReplyCancel
  3. Shari
    August 23, 2018 at 5:19 am

    DECLARE @AnyDate DATETIME
    SET @AnyDate =’9 Nov 2018′
    –GETDATE()

    SELECT @AnyDate AS ‘Input Date’,
    DATEADD(q, DATEDIFF(q, 0, @AnyDate), 0)
    AS ‘Quarter Start Date’

    ReplyCancel

Leave a Reply

CAPTCHA

*

Anvesh Patel
Anvesh Patel

Database Engineer

October 11, 2017 3 Comments SQL Puzzle, , , , , , , , , ,