Calculate The Last Day Of The Prior Quarter

September 29, 2023 Post a Comment

What's the most efficient way to calculate the last day of the prior quarter? Example: given the date 11/19/2008, I want to return 9/30/2008. Platform is SQL Server

Solution 1:

If @Date has the date in question

SelectDateAdd(day, -1, dateadd(qq, DateDiff(qq, 0, @Date), 0))

EDIT: Thanks to @strEagle below, simpler still is:

Selectdateadd(qq, DateDiff(qq, 0, @Date), -1)

Solution 2:

Actually simpler is:

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

Solution 3:

Get the current date

SELECTCONVERT(DATE,GETDATE()) [CurrentDate]

Get the 1st date of the quarter for the current date

SELECTCONVERT(DATE, DATEADD(QQ, DATEDIFF(QQ, 0, GETDATE())  ,0)) [Current Quarter 1st Date]

Get the last date of the quarter for the current date

SELECTCONVERT(DATE,DATEADD(d, -1, DATEADD(q, DATEDIFF(q, 0, GETDATE()) +1, 0))) [Current Quarter LastDate]

Get the 1st date of the next quarter for the current date

SELECTCONVERT(DATE, DATEADD(QQ, DATEDIFF(QQ, 0, GETDATE()) +1 ,0)) [Next Quarter 1st Date]

Get the last date of the next quarter for the current date

SELECTCONVERT(DATE,DATEADD(d, -1, DATEADD(q, DATEDIFF(q, 0, GETDATE()) +2, 0))) [Next Quarter LastDate]

Solution 4:

I came up with this (tested for all months):

select dateadd(dd,-1,dateadd(qq,datediff(qq,0,'11/19/2008'),0)),
       dateadd(dd,-1,dateadd(qq,datediff(qq,0,'10/19/2008'),0)),
       dateadd(dd,-1,dateadd(qq,datediff(qq,0,'12/19/2008'),0))

It might turn out to be the simplest.

Solution 5:

convert(varchar, dateadd(dd,-1,dateadd(qq,1,DATEADD(qq, DATEDIFF(qq,0,YOUR_DATE), 0))),112)

you also can change 112 base on this below list

SELECTconvert(varchar, getdate(), 100) -- mon dd yyyy hh:mmAM (or PM)-- Oct  2 2008 11:01AMSELECTconvert(varchar, getdate(), 101) -- mm/dd/yyyy - 10/02/2008                  SELECTconvert(varchar, getdate(), 102) -- yyyy.mm.dd – 2008.10.02           SELECTconvert(varchar, getdate(), 103) -- dd/mm/yyyySELECTconvert(varchar, getdate(), 104) -- dd.mm.yyyySELECTconvert(varchar, getdate(), 105) -- dd-mm-yyyySELECTconvert(varchar, getdate(), 106) -- dd mon yyyySELECTconvert(varchar, getdate(), 107) -- mon dd, yyyySELECTconvert(varchar, getdate(), 108) --  hh:mm:ssSELECTconvert(varchar, getdate(), 109) -- mon dd yyyy hh:mm:ss:mmmAM (or PM)-- Oct  2 2008 11:02:44:013AM   SELECTconvert(varchar, getdate(), 110) -- mm-dd-yyyySELECTconvert(varchar, getdate(), 111) -- yyyy/mm/ddSELECTconvert(varchar, getdate(), 112) -- yyyymmddSELECTconvert(varchar, getdate(), 113) -- dd mon yyyy hh:mm:ss:mmm--  02 Oct 2008 11:02:07:577     SELECTconvert(varchar, getdate(), 114) -- hh:mm:ss:mmm(24h)SELECTconvert(varchar, getdate(), 120) -- yyyy-mm-dd hh:mm:ss(24h)SELECTconvert(varchar, getdate(), 121) --  yyyy-mm-dd hh:mm:ss.mmmSELECTconvert(varchar, getdate(), 126) -- yyyy-mm-ddThh:mm:ss.mmm--  2008-10-02T10:52:47.513-- SQL create different date styles with t-sql string functionsSELECT replace(convert(varchar, getdate(), 111), '/', ' ') -- yyyy mm ddSELECTconvert(varchar(7), getdate(), 126)                 -- yyyy-mmSELECTright(convert(varchar, getdate(), 106), 8)          -- mon yyyy

Lab Sql