October 22, 2013 at 12:37 pm
I am trying to get the begining and end of a fiscal year based on User input date.
@para is a date in table based on which the fiscal year as to be calculated.
User date will be compared with @para and then the fiscal year calculated.
Scenario :
If @para is 2014-02-01 then
Begfiscal will be 2013-02-01
Endfiscal will be 2012-01-31
I am able to get the begining year but the End year is causing a problem.
Here is logic which gets me to Fiscal End year as 2012-02-01 . Don't know how to remove another day....
Please advise
CASE
WHEN CONVERT ( DATETIME, CONVERT ( VARCHAR(5), @para, 1 ) + '/' + CAST ( YEAR ( @UserInputDt ) AS VARCHAR ), 101 ) <= @UserInputDt
THEN
CONVERT ( DATETIME, CONVERT ( VARCHAR(5), @para, 1 ) + '/' + CAST ( YEAR ( @UserInputDt ) + 1 AS VARCHAR ), 101 )
ELSE
CONVERT ( DATETIME, CONVERT ( VARCHAR(5), @para, 1 ) + '/' + CAST ( YEAR ( @UserInputDt ) AS VARCHAR ), 101 )
END AS FiscalEndDt
October 22, 2013 at 1:09 pm
Your scenario isn't entirely clear to me.
You do not explain what the value @UserInputDt has to do with @para (=2014-02-01).
Why is the result for Begfiscal 2013-02-01 and for Endfiscal 2012-01-31?
Wouldn't it be easier to use datetime functions (such as dateadd) instead of manipulating strings?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 22, 2013 at 2:16 pm
As Koen said, date functions are much better for this than manipulating strings.
Based on your example, this is a way to calculate it.
DECLARE @para date = '20140201'
SELECT @para,
DATEADD( YEAR, -1, @para) Begfiscal,
DATEADD( DAY, -1, DATEADD( YEAR, -2, @para)) Endfiscal
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply