Need to understand the logic for T-SQL Query

Question

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Jay Pete (5/24/2012)
Can Anyone explain the logic to behind to get the first day of the month
SELECT dateadd(MM,datediff(MM,0,getdate()),0)

To understand this you first have to break it apart.

The second parameter inside the outer dateadd is

datediff(MM,0,getdate())

This gets the number of months since the "0" date or 1/1/1900. As of today's posting that is 1,348.

Then you simply use the dateadd function to add that many months to 1/1/1900. So if you add 1,348 months to 1/1/1900 you will get 5/1/2012.

Make sense now?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Paul White SSC Guru Points: 150467 More actions · Answer 2

Sean Lange (5/24/2012)
This gets the number of months since the "0" date or 1/1/1900. As of today's posting that is 1,348.

Just to emphasise, the reason it works is that this part of the query returns a whole number of months.

Jay Pete SSCommitted Points: 1945 More actions · Answer 3

Thanks Sean for your posting.I make sense now.

Would you elaborate more to explain me?

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

I suspect Paul will link to this article. One I keep close at hand myself.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.