Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Convert and CAst Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, October 22, 2013 12:37 PM
 SSC-Addicted Group: General Forum Members Last Login: 2 days ago @ 9:48 AM Points: 406, Visits: 1,219
 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 thenBegfiscal will be 2013-02-01Endfiscal will be 2012-01-31I 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
Post #1507287
 Posted Tuesday, October 22, 2013 1:09 PM
 SSCoach Group: General Forum Members Last Login: 2 days ago @ 2:09 AM Points: 15,501, Visits: 13,163
 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? How to post forum questions.Need an answer? No, you need a question.What’s the deal with Excel & SSIS?Member of LinkedIn. My blog at SQLKover. MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1507303
 Posted Tuesday, October 22, 2013 2:16 PM
 SSCrazy Eights Group: General Forum Members Last Login: Today @ 12:11 PM Points: 8,215, Visits: 17,775
 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` Luis C.General Disclaimer:Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?How to post data/code on a forum to get the best help: Option 1 / Option 2
Post #1507360

 Permissions