Dynamic Query to get Fiscal(Financial Year) SQL Server 2005
Below dynamic query will set StartDate and EndDate to 1st of March to 30th April as UK financial year.
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = DATEADD(dd,0, DATEDIFF(dd,0, DATEADD( mm, -(((12 + DATEPART(m, getDate())) - 4)%12), getDate() ) - datePart(d,DATEADD( mm, -(((12 + DATEPART(m, getDate())) - 4)%12),getDate() ))+1 ) )
SET @EndDate = DATEADD(SS,-1,DATEADD(mm,12,@StartDate))
Note: to get different dates you can play around StartDate query to get different dates based on your requirements.
1 Year Ago
Related Article:SYNC SQL SERVER 2005 TABLES WITH ORACLE 11G
is a Databases discussion thread by pardeep3dec that has 1 reply, was last updated 4 months ago and has been tagged with the keywords: oracle, sql, server, 2005, sync, table.
2 things: March 1st to April 30th is a 2 month period, if that's the UK financial year what happens to the rest 10 months? I'm guessing this is a typo, as your code proves.
second thing is why not just set dateformat and set the date as a string?
set @StartDate = convert(datetime,year(getdate()) + '-03-01')
PS: Please use the CODE tags around your code. Thanks.
1) The Financial year in UK is March to April, that means(current financial year) 01/03/2011 to 31/04/2012, so it's not only two months. for next year it will be 01/03/2012 to 31/04/2013. if you run above script you will get idea.
2) Both dates are in datetime datatype, so if you want to display in different format you can use Convert() function.