Wednesday, February 26, 2014

Getting the first date of the month in SQL without any functions

One of the date columns in my database is an integer as it is a dimension key
I was trying to get some results grouped for the whole month by this date integer column.

This is how I went about getting it in an easier manner without using any DATEADD and DATDIFF functions

Let us say that the date column name is dim_date_key which is an integer data type

So I used the following SQL code to get the sum of the amount for the month

select sum(amount), dim_date_key/100*100 + 1 month_start_Date from table_name
where dim_date_key/100*100 + 1 >= 20130401
group by dim_date_key/100*100 + 1

Here if we just look at the expression dim_process_date_key/100*100 +1 in mathematical terms it is confusing as to how this can get the start of the month.

But the key here is the integer data type of the date column.

  • When the expression is evaluated, the expression dim_process_date_key/100 gets evaluated first.  This gives the answer as 201304 since the data type is an integer.
  • Then the expression 201304 * 100 is evaluated which yeilds the result as 20130400 which is an integer.  
  • Then the expression 20130400 + 1 is evaluated which yeilds the start date of the month which is 20130401

No comments:

My Microsoft MVP Award

At the beginning of this month, I received an email from #microsoft telling me that I have received the Most Valuable Professional (MVP Awa...