CODEWITHSUNDEEP
sqlsql-servert-sqldatetime
NCollinsTE
NCollinsTE

Reputation: 309

SQL SELECT First Day and Last Day of the Month.

Dearest professionals,

I have a query built to get the first and last day of the current month, but I'm having an issue with the time stamp for the First Day of the month. 

declare @FirstDOM datetime, @LastDOM datetime

set @FirstDOM = (select dateadd(dd,-(day(getdate())-1),getdate()) )
set @LastDOM = (select dateadd(s,-1,dateadd(mm,datediff(m,0,getdate())+1,0)))

Since it's February of 2015, I would like to get results of:

@FirstDOM = 2015-02-01 00:00:00.000
@LastDOM = 2015-02-28 23:59:59.000

@LastDOM is correct, but I'm not getting the zeroes for the time stamp portion of @FirstDOM, I'm getting the correct date, but the time of the time I run the script. Say it's 8:50 a.m., I get:

2015-02-01 08:50:49.160

What is the best way to fix this little snafu?

Regards,

Nick

Upvotes: 6

Views: 90227

Answers (6)

philomath
philomath

Reputation: 11

I was looking for function to get First day of the month and stumbled on this; I came up with the below approach.

SELECT DATEADD(DAY, 1, EOMONTH(GETDATE(),-1))   AS 'FirstDayOfMonth'
      ,EOMONTH(GETDATE())                       AS 'LastDayOfMonth'  ;

Upvotes: 1

Hijesh V L
Hijesh V L

Reputation: 269

multiple ways to do it

select dateadd(d,(day(getdate())-1)*-1,getdate())

I prefer the second method, output comes without a timepart

select dateadd(d,1,eomonth(getdate(),-1))

Upvotes: 1

Bamidelzz
Bamidelzz

Reputation: 106

You can get the First and Last Day of the month using this:

SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)  ----First Day
SELECT DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0)) ----Last Day

Upvotes: 7

Joe Platano
Joe Platano

Reputation: 614

in addition to other answers, since SQL-Server 2012, Microsoft offers

EOMONTH ( start_date [, month_to_add ] )

More details on msdn

--

of topic: I stumbled on this question looking for First Day of Month in SQL which has been answered by others

Upvotes: 5

Suraj Singh
Suraj Singh

Reputation: 4059

declare @FirstDOM datetime, @LastDOM datetime

set @FirstDOM = (select dateadd(d,-1,dateadd(mm,datediff(m,0,getdate()),1 )))
set @LastDOM = (select dateadd(s,-1,dateadd(mm,datediff(m,0,getdate())+1,0))) 
Print @FirstDOM
Print @LastDOM

Upvotes: 6

Swapnil
Swapnil

Reputation: 434

Convert @FirstDOM to DATE as below:

declare @FirstDOM datetime, @LastDOM datetime

set @FirstDOM = (select CONVERT(DATE,dateadd(dd,-(day(getdate())-1),getdate())) )
set @LastDOM = (select dateadd(s,-1,dateadd(mm,datediff(m,0,getdate())+1,0))) 
SELECT @FirstDOM,@LastDOM

I hope this will help!

Thanks,

Swapnil

Upvotes: 6

Related Questions