CODEWITHSUNDEEP
sqlsql-server-2008
whytheq
whytheq

Reputation: 35577

Return the first of the month that is two full complete months back

(slight invitation to air opinion)

Is this the most efficient/accepted way of returning the first of the month that is two full months back:

SELECT DATEADD(mm,-2,GETDATE() - DAY(GETDATE())) + 1

Upvotes: 0

Views: 50

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

Just two ops:

SELECT DATEADD(month,DATEDIFF(month,'20010301',GETDATE()),'20010101')

Which exploits the relationship between the two fixed dates to include the removal of two months whilst "normalizing" the date to remove day and time information.

Where this is nice is you can use it to achieve other tricks that are difficult to get correct without jumping through a lot more hoops. Such as the last day of the month two months ago:

SELECT DATEADD(month,DATEDIFF(month,'20010301',GETDATE()),'20010131')

which gets things right even if that month has fewer than 31 days

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460238

Not sure if this is faster, but ...

SELECT DATEADD(m, DATEDIFF(m, 0, GETDATE()) -2, 0)

SQL-Fiddle

Calculate the difference in month's from '1900-01-01':

DATEDIFF(m, 0, GETDATE())  -- 1356

Add the difference to '1900-01-01' minus two months

Upvotes: 3

Related Questions