6

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
NCollinsTE
  • 309
  • 1
  • 3
  • 13
  • 1
    Since getdate() is datetime your query will also return datetime. One way would be to convert your result to date,then to varchar and add time manually, or you can get the same with datepart & dateadd. – glaeran Feb 10 '15 at 14:02
  • 2
    FWIW, '23:59:59.000' isn't the last instant in a month. You might miss some data this way. – Mike Sherrill 'Cat Recall' Feb 10 '15 at 14:09
  • http://stackoverflow.com/questions/113045/how-to-return-the-date-part-only-from-a-sql-server-datetime-datatype – Mike Nakis Feb 10 '15 at 14:12
  • 3
    If you're working with continuous data like datetimes, it's almost always better to use semi-open intervals (*inclusive* start point, *exclusive* end point) - the values you need to calculate tend to be easier to find, and it's a lot easier to verify that you're not accidentally including particular values in *multiple* ranges or no ranges, where ranges are meant to be exclusive and exhaustive. So could switching to using `>=@FirstDOM and < @FirstDONextM` work for you? – Damien_The_Unbeliever Feb 10 '15 at 14:23
  • @Damien_The_Unbeliever, that worked out best for me as you suggested. ( >=FirstDOM and < FirstDONextM ) – NCollinsTE Feb 12 '15 at 20:50
  • Tip: If you are going to play with `GETDATE` repeatedly, you may want to grab a value and work with it, e.g. `declare @Now as DateTime = GetDate();`. It can be really hard to track down bugs when someone runs your code around midnight and they pick up various dates. – HABO Mar 03 '15 at 19:44

6 Answers6

6
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
Suraj Singh
  • 4,041
  • 1
  • 21
  • 36
5

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

Swapnil
  • 424
  • 2
  • 13
5

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
Bamidelzz
  • 76
  • 1
  • 2
4

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

Joe Platano
  • 586
  • 1
  • 14
  • 27
0

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))

Hijesh V L
  • 191
  • 1
  • 5
0

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'  ;
toyota Supra
  • 3,181
  • 4
  • 15
  • 19
philomath
  • 1
  • Thank you for contributing to the Stack Overflow community. This may be a correct answer, but it’d be really useful to provide additional explanation of your code so developers can understand your reasoning. This is especially useful for new developers who aren’t as familiar with the syntax or struggling to understand the concepts. **Would you kindly [edit] your answer to include additional details for the benefit of the community?** – Jeremy Caney Aug 17 '23 at 02:33