First of the month

In your SQL programming life, have you ever run into the problem of needing to calculate the first day of the current month? Well, if you haven’t dammit! Go run into it!!

If you did run into it, you probably googled for it, and found a few pretty good articles or scripts telling you how to do it. And you moved on with life.

But did you really understand how it calculated the first of the month? If you didn’t, well – you have come to the wrong place!! I am going to try to explain, but this is going to be shallow and the only purpose is to increase my fame among other SQL geeks.

So, what you probably used is something like this. (In case you used some character manipulations to get the first of the month: shame! shame!! shame!!! Don’t do it like that. Use this!!)

SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0);

The piece that they probably didn’t tell you (or may be they did and you didn’t pay attention) is that those two zero’s are really dates. In DATETTIME datatype (and other similar data types) 0 represents January 1, 1900. So I could as well write the same query like this

SELECT DATEADD(mm,DATEDIFF(mm,'19000101',GETDATE()),'19000101');

A little less pretty, and more typing. Besides, someone might actually figure out what you are trying to do if you did it this way. So why bother. Stick with that zero and add to your job security!

Now go take a quick look at the DATEDIFF and DATEADD functions in MSDN. Here are the links – but don’t get absorbed in the rivetting story they are telling you there. Just take a quick look and come back.

http://msdn.microsoft.com/en-us/library/ms189794.aspx
http://msdn.microsoft.com/en-us/library/ms186819.aspx

So DATEDIFF as we used it here is calculating how many months there are between today and that magical date of January 1, 1900. One key thing to remember is that it is really calculating the number of BOUNDARY crossings. In other words, to get from January 1, 1900 to July 25, 2013, how many month-end-to-new-month-beginning boundaries did you have to cross. In case you are curious, the answer is 1362; but you don’t need to know and you shouldn’t care.

Now that you know that, the outer function – DATEADD – is simply adding that many months to January 1, 1900. And lo and behold, you get the first of the current month.

If you think this is geeky stuff and you don’t need to know it: let us say you wanted to programmatically get the fifth of the current month. If you followed what I said above, you would know that you can make one tiny change and you are there!

SELECT DATEADD(mm,DATEDIFF(mm,'19000101',GETDATE()),'19000105');

There is a lot more to the story, but I am hungry, and lunch is here, and if I say too much, it will take away from the shallowness of the blog. So we will pick up on this another time.

Edit: As promised, I did do a second part of this date thingie. If you have nothing better to do, go check that out here: https://myshallowsqlblog.wordpress.com/beginning-of-anything/