How to get the last day of the quarter

Question

Hi All,
I have a CALENDAR_DATE , based on this date i want to create the LAST_DAY_OF_THE_QUARTER_IND ,please advice,
Thanks

Answer 1

DECLARE @date date = '20201202'
SELECT convert(date, 
         datename(year, @date) + 
           CASE datepart(quarter, @date)
              WHEN 1 THEN '0430'
              WHEN 2 THEN '0630'
              WHEN 3 THEN '0930'
              WHEN 4 THEN '1231'
           END)

Answer 2

DECLARE @date date = '2020-12-01';
SELECT DATEADD (dd, -1, DATEADD(qq, DATEDIFF(qq, 0, @date) +1, 0));

Answer 3

Hi @blackhills ,

Please refer to:

DECLARE @TB TABLE(TIME DATETIME)  
INSERT INTO @TB VALUES('2013-02-09 11:22:59.997')  
INSERT INTO @TB VALUES('2013-05-07 13:12:51.333')  
INSERT INTO @TB VALUES('2013-07-07 15:42:22.633')  
INSERT INTO @TB VALUES('2013-10-20 19:29:22.783')   
SELECT *,CASE WHEN DATEPART(QQ,TIME)=1 THEN DATEADD(D,-1,'2013-04-01 23:59:59.997')       
              WHEN DATEPART(QQ,TIME)=2  THEN DATEADD(D,-1,'2013-07-01 23:59:59.997')        
              WHEN DATEPART(QQ,TIME)=3  THEN DATEADD(D,-1,'2013-10-01 23:59:59.997')         
              WHEN DATEPART(QQ,TIME)=4  THEN DATEADD(D,-1,'2014-01-01 23:59:59.997')  END AS QUARTER             
FROM @TB  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 4

Thanks All,
All the scenarios worked fine, but it will only let me select only one as Answer. Thanks again,