SQL Date Scriptlets….

Posted on by

Convert calendar month number to financial year month number i.e. April = 1 rather than 4
In the following DTTM = Your date field

(MONTH([DTTM])+8)%12+1

Obtain Financial year from a date.

CASE WHEN MONTH(DTTM) IN (1,2,3) THEN CONVERT(CHAR(4),YEAR(DTTM)-1)+'/'+CONVERT(CHAR(4),(YEAR(DTTM))) 
ELSE CONVERT(CHAR(4),YEAR(DTTM)) +'/'+CONVERT(CHAR(4),YEAR(DTTM)+1) END as fyear

Some Automatic Dates:

--first day of current month
DATEADD(MM, DATEDIFF(M, 0, GETDATE()), 0)  

--first day of previous months
DATEADD(MM, DATEDIFF(M, 0, GETDATE())-1 , 0)  

--last day of previous month
DATEADD(ms,-2,DATEADD(MM, DATEDIFF(M, 0, GETDATE()) , 0))  

--tomorrow evening
DATEADD(ms,-2,DATEADD(dd, DATEDIFF(d, 0, GETDATE())+2 , 0))

…or swap the getdate() to a field to get the appropriate conversion from a field.

Select the first day of the current financial year…

case when month(DTTM) between 4 and 12 
then DATEADD(MM, DATEDIFF(M, 0, DTTM)-(month(DTTM)-4) , 0) 
else   DATEADD(MM, DATEDIFF(M, 0, DTTM)-(month(DTTM)+8) , 0) end

Leave a Reply