Select the first and the last day of a month in sql server for a month already stored in SQL Database

Question

I have a small idea for that but i don't know how to interpret it in sql server (MSDN).

if (Month < ActualMonth)
{
  (StartDateReq = FirstDayMonth);
  (EndDatereq) = LastDayMonth);
}
else if (Month = currenTimeStamp)
 {
  (EndDateReq = currentTimeStamp);
 }

Do you want to select all rows with dates in the previous month + dates that are today? — Peter Morris, Oct 27 '15 at 09:47
See this previous question http://stackoverflow.com/questions/11743810/how-to-get-first-and-last-day-of-previous-month-with-timestamp-in-sql-server — Peter Morris, Oct 27 '15 at 09:50
I just want to have the first and last day if the Month(which was in database) is lower than the actual month — Gurnavdeep Singh, Oct 27 '15 at 10:19
For example : we are in October, but in database i have september. I just want to have 01/09/2015 and 30/09/2015. And if in database we have also a data who have October, i want to have just the currentday — Gurnavdeep Singh, Oct 27 '15 at 10:22

score 0 · Answer 1 · answered Oct 27 '15 at 11:04

If you have a date in SQL Server, you can get the first and last day of the month using:

select dateadd(day, 1 - day(datecol), datecol) as firstday,
       dateadd(day, -1, dateadd(month, 1, dateadd(day, 1 - day(datecol), datecol))) as lasstday

In SQL Server 2012+, you can use the function EOMONTH() for the last day of the month.

score 0 · Answer 2 · answered Oct 27 '15 at 13:13

Drawing on Gordon's answer:

DECLARE @myDate Date = '2015-10-25'

select DATEADD(DAY, 1 - DAY(@myDate), @myDate) as firstday,
       CASE WHEN YEAR(@myDate) = YEAR(GETDATE()) AND 
                 MONTH(@MyDate) = MONTH(GETDATE()) 
       THEN @MyDate
       ELSE DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEADD(DAY, 1 - DAY(@myDate), @myDate))) 
       END as lasstday

Select the first and the last day of a month in sql server for a month already stored in SQL Database

2 Answers2