NCollinsTE NCollinsTE - 1 month ago 13
SQL Question

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

Answer Source

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