Beginning of anything

And, I mean literally ANYTHING!! Before you get thoroughly confused (which by the way would have been fun to watch, if only I could see you), this is a continuation of my previous SSB (SSB means Shallow SQL Blog) https://myshallowsqlblog.wordpress.com/first-of-the-month/ I promise you, this will be even shallower and meaningless.

In that blog, you saw how to find the first of a month. Even though I used getdate() function so I get the first of the current month, you can replace that getdate() with any date, and it will give you the first of the month in which that date happens to be.

Now we are going to move on to bigger and better things – the beginning of literally ANYTHING. What I mean is, beginning of the month, beginning of the year, beginning of the quarter, beginning of the week, or even, strange as it may sound, “beginning of the hour”. Yeah, that is not proper English, so sue me! But wait until you hear my explanation before you run to the court house!!

Before I tell you about this awesome “beginnings”, remember that MSDN link to DATEADD (or DATEDIFF) that I gave you in the previous blog? If you don’t remember, here it is again: http://msdn.microsoft.com/en-us/library/ms186819.aspx
It lists the various dateparts and their abbreviations. We can use those dateparts and use the same pattern as what we used for finding the first of the month. Like so

-- beginning of the month
SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0);

-- beginning of the quarter
SELECT DATEADD(qq,DATEDIFF(qq,0,GETDATE()),0);

-- beginning of the year
SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0);

-- beginning of the week. Yes, Monday. Why is that?
SELECT DATEADD(ww,DATEDIFF(ww,0,GETDATE()),0);


-- And now, beginning of the day
SELECT DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0);

-- Beginning of the hour
SELECT DATEADD(hh,DATEDIFF(hh,0,GETDATE()),0);

-- Beginning of the minute
SELECT DATEADD(mi,DATEDIFF(mi,0,GETDATE()),0);

Really, the beginning of the Hour is just the date with everything more granular than the hour stripped off. So right now if it is 2013-07-25 13:37:34.430, then beginning of the hour is 2013-07-25 13:00:00.000 and beginning of the minute is 2013-07-25 13:37:00.000

Now, if you got a little ambitious, and decided that you want to calculate the beginning of the second or beginning of the millisecond, that darn thing is going to fail with an error message something like this:
Msg 535, Level 16, State 0, Line 1
The datediff function resulted in an overflow.
Bleh! How do you fix that?

Glad you asked!! If you recall the DATEDIFF in our formulas is the difference between January 1, 1900 and the current date (or the date that you put in in place of GETDATE()). And that difference expressed in seconds or milliseconds is way too large for an INT to hold.

So we will simply pick another reference date. Like July 24, 2013 (which happens to be my birthday. Couldn’t get any more auspicious that now, could it?)

SELECT DATEADD(ss,DATEDIFF(ss,'20130724',GETDATE()),'20130724');
SELECT DATEADD(ms,DATEDIFF(ms,'20130724',GETDATE()),'20130724');

So there you have it! Calculate the beginning of ANYTHING using T-SQL.

Next time, we will discuss how to calculate the END of anything (and yes, including the end of the world, if you are worried about that), and how to offset things – for example, if your financial year starts on March 1st of each year, how to account for that in your calculations.

But, don’t get your hopes up too high! If I told you all of that, then someone might accuse me of not being shallow enough. We can never let THAT happen now, can we?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s