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