SQL return first day of last month and current month @ 00:00:00

Question

I am using this code

declare @mydate datetime select @mydate = getdate()
declare @startdate varchar(20)  SELECT @StartDate = CONVERT(varchar(20),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,-1,@mydate)),120)
declare @enddate varchar(20) SELECT @EndDate = CONVERT(varchar(20),DATEADD(dd,-(DAY(@mydate)-1),@mydate),120)
Select @startdate as 'Start Date', @enddate as 'End Date'

To determine the first day of the previous and current month for a report, but it gives a time based on the current time. How can I get

2011-04-01 00:00:00
&
2011-05-01 00:00:00

?

@MT0 Since I asked mine first shouldn't that question be a duplicate of mine? I get updates and long-tail rep of this. I was first. — gooddadmike, Mar 01 '16 at 15:03
Yes, you asked the question first but the other question has some more detailed answers. If it was the other way round then I would have voted to close the other question. See http://meta.stackoverflow.com/questions/251938/should-i-flag-a-question-as-duplicate-if-it-has-received-better-answers — MT0, Mar 01 '16 at 15:17
I was not aware. The meta has spoken! I also agree. My reasons were selfish. The logic from MSE wins. I vote to be flagged as dup. Thanks for your democratic response and "don't be a jerk"-ness. I love you. OK Bye! — gooddadmike, Mar 01 '16 at 16:50

score 31 · Accepted Answer · edited May 23 '17 at 12:07

31

Use the DATEADD/DATEDIFF trick:

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

edited May 23 '17 at 12:07

Community

1
1

answered May 09 '11 at 19:04

gbn

422,506
82
585
676

5

+1 for not using the string method. However, if he's using MSSQL2008 he can just convert to `DATE` – Matthew May 09 '11 at 19:25

Simon Mokhele · Answer 2 · 2015-03-17T09:17:29.937

0

If you are using MYSQL Data base

# First day of the previous month
SELECT concat(LAST_DAY(NOW() - INTERVAL 1 MONTH),' ','00:00:00') as 'PreviousMonthFirstDay';

# Last day of the previous month
SELECT concat(LAST_DAY(NOW() - INTERVAL 1 MONTH),' ','00:00:00') as 'PreviousMonthLastDay';

Reference MySQL First day of previous month

edited Mar 17 '15 at 09:17

answered Mar 09 '15 at 08:03

Simon Mokhele

3,913
3
21
22

score 0 · Answer 3 · answered Apr 02 '15 at 09:53

0

From SQL2012, there is a new function introduced called EOMONTH. Using this function the first and last day of the month can be easily found.

select DATEADD(DD,1,EOMONTH(Getdate(),-1)) firstdayofmonth, EOMONTH(Getdate()) lastdayofmonth

answered Apr 02 '15 at 09:53

Diwakar

121
1
2

score 0 · Answer 4 · edited May 09 '11 at 19:09

0

You can change your last statement to the one below to achieve the results:

Select SUBSTRING(@startdate,1,11) + '00:00:00' as 'Start Date', 
       SUBSTRING(@enddate,1,11)+ '00:00:00' as 'End Date'

edited May 09 '11 at 19:09

gbn

422,506
82
585
676

answered May 09 '11 at 18:33

Shak Kash

9
1

SQL return first day of last month and current month @ 00:00:00

4 Answers4