Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Convert and CAst Expand / Collapse
Author
Message
Posted Tuesday, October 22, 2013 12:37 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 215, Visits: 638
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
Post #1507287
Posted Tuesday, October 22, 2013 1:09 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:49 PM
Points: 13,283, Visits: 10,156
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


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:23 PM
Points: 3,354, Visits: 7,250
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1507360
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse