1

I've been writing queries to truncate the time from a given datetime for years now, based on answers like this one and this one (and many other places), that groups a couple functions like

SELECT DATEADD(day, 0, DATEDIFF(day, 0, getdate()))

and it always gives the right answer.

I figured I could translate the same logic to finding the first of the current month by using month instead of day, but it's giving me a weird date for the result: 1903-10-17, instead of 2015-05-01.

Community
  • 1
  • 1
Danny
  • 1,740
  • 3
  • 22
  • 32

1 Answers1

0

My parameters have always been in the wrong order.

It turns out the format for DATEADD I've been using all these years is wrong, and it's only been working because it's using the day datepart. Casting an int to a date increments the day:

SELECT CAST(0 AS datetime) = '1900-01-01 00:00:00.000'
SELECT CAST(1 AS datetime) = '1900-01-02 00:00:00.000'
SELECT CAST(2 AS datetime) = '1900-01-03 00:00:00.000'

I should be using DATEADD(d, DATEDIFF(d, 0, getdate()), 0) - the parameters are (datepart, number, date), as laid out here at MSDN.

Writing it as SELECT DATEADD(month, DATEDIFF(month, 0, getdate()), 0) gives the expected result of 2015-05-01.

Danny
  • 1,740
  • 3
  • 22
  • 32
  • A good chunk of our servers are still running 2005, so I don't think that'll work for us in general, but I'll take another look at your post, @AaronBertrand. – Danny May 29 '15 at 21:19
  • And you only have to replace the second `GETDATE()`, not the first. Also see [this](http://sqlperformance.com/2012/09/t-sql-queries/what-is-the-most-efficient-way-to-trim-time-from-datetime) and [this](http://sqlperformance.com/2012/10/t-sql-queries/trim-time) for discussions about truncating the time. – Aaron Bertrand May 29 '15 at 22:05