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 Journeyman Group: General Forum Members Last Login: Today @ 11:14 AM Points: 84, Visits: 264
 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
 SSChampion Group: General Forum Members Last Login: Today @ 8:28 AM Points: 11,229, Visits: 8,082
 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 LessThanDot. MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1507303
 Posted Tuesday, October 22, 2013 2:16 PM
 SSCommitted Group: General Forum Members Last Login: Today @ 8:42 PM Points: 1,902, Visits: 4,222
 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.Please don't trust me, test the solutions I give you before using them.Forum Etiquette: How to post data/code on a forum to get the best help
Post #1507360

 Permissions