I have code that returns fiscal year in an object using Oracle and now need to replicate but for a SQL server 2008 database. I’ve tried different things but keep getting errors.
Oracle
CASE WHEN (SUBSTR(SYSADM.OP_OFF_SIG_VIEW.OFF_INPUT_DATE, 4,2)) <'04'
THEN TO_NUMBER(SUBSTR(SYSADM.OP_OFF_SIG_VIEW.OFF_INPUT_DATE, 7,4)-1) || '/' || TO_NUMBER(SUBSTR(SYSADM.OP_OFF_SIG_VIEW.OFF_INPUT_DATE, 7,4))
ELSE TO_NUMBER(SUBSTR(SYSADM.OP_OFF_SIG_VIEW.OFF_INPUT_DATE, 7,4)) || '/' || TO_NUMBER(SUBSTR(SYSADM.OP_OFF_SIG_VIEW.OFF_INPUT_DATE, 7,4)+1)
END
The first thing that I would say is that your Oracle expression is a bit of a convoluted mess mate.
I’ll first of all assume that OFF_INPUT_DATE is a date datatype ('cos nobody is ever daft enough to store dates in a string are they )
Using the substring directly on a date isn’t a great idea as it will use implicit datatype conversion, which may well generate some very unexpected results over time.
To extract a month from a date, use:
EXTRACT(MONTH FROM YOURDATEHERE) this will return a number
Which will allow you to use
case when EXTRACT(MONTH FROM YOURDATEHERE) <4
then blah…
Now, as to the outputs,
a combination of the add_months function and the to_char function should do it for you.
e.g.
to_char(add_months(yourdatehere,-12),‘yyyy’)
will give you the previous year.
to_char(yourdatehere,‘yyyy’)
will give you ‘this’ year.
IF someone has taken the VERY poor decision to store these values in a non date column, then convert that value into a date (using the to_date function) then perform the calculations as above.
Now, the real solution, if the data is not a date datatype is to get the data model fixed, but that can be easier said than done, I’d settle for slapping whoever made the decision in the first place ;).
As for a sql server solution, that would depend on whether the column is a date datatype or something else. But if it’s a date, then look up the syntax for the DateAdd function.
ETA,
In fact, even easier (for your Oracle solution):
)