CODEWITHSUNDEEP
sqlsql-server
Dereck
Dereck

Reputation: 783

SQL Server - Get calander month begining and end

I've tried searching but cannot find anything.

I am trying to get the first and last date of the calander month.

So for example the calander month for January 2020 actually starts on December 30th 2019 and ends on February 2nd 2020. (Week 1 - 5)

|---------------------|-------------------|-------------------|
|      Week number    |     From Date     |     To Date       |
|---------------------|-------------------|-------------------|
|       Week 01       | December 30, 2019 | January 5, 2020   |
|---------------------|-------------------|-------------------|
|       Week 05       | January 27, 2020  | February 2, 2020  |
|---------------------|-------------------|-------------------|

Using this website to get week numbers

Is this possible?

Many thanks.

Upvotes: 1

Views: 504

Answers (2)

Somendra Kanaujia
Somendra Kanaujia

Reputation: 824

If you are using MSSQL-2012 or onwards.

DECLARE @DATE DATETIME='29-JAN-2020'

SELECT  DATEADD(DAY, 2 - CASE WHEN DATEPART(WEEKDAY, @DATE-DAY(@DATE)+1)=1 THEN 8 ELSE DATEPART(WEEKDAY, @DATE-DAY(@DATE)+1) END, CAST( @DATE-DAY(@DATE)+1 AS DATE)) [MONTH_START_DATE], 
DATEADD(DAY, 8 - CASE WHEN DATEPART(WEEKDAY, EOMONTH(@DATE))=1 THEN 8 ELSE DATEPART(WEEKDAY, EOMONTH(@DATE)) END , CAST(EOMONTH(@DATE) AS DATE)) [MONTH_END_DATE];

You can try on below link:-

https://dbfiddle.uk/?rdbms=sqlserver_2012&fiddle=9747ea25d0d0bc343be8dbcc90803303

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269743

You can use this logic:

select convert(date, dateadd(day, 1 - day(getdate()), getdate())) as month_first,
       dateadd(day, 1, eomonth(getdate(), -1)) as alternative_month_first,
       eomonth(getdate()) as month_last

Of course, you would use whatever date you wanted instead of getdate().

Upvotes: 0

Related Questions