Apologies if this is very simple and part of the basic SQL training but i'm very new to User Defined Functions (SQL 2000)
I am trying to create a function to take two variables, a varchar financial year value and an nvarchar financial month value and convert this to a datetime format, with the date automatcially being the 1st of that month.
Example: Take '2010/2011' and 2 (2nd financial month) as variables and convert to 2010-05-01 00:00:00.000
The function i am trying to create is below and i know that the code inside the function works fine, as i've tested it. The problem seems to be that the substring function is not working as it should be within the udf i am trying to create. instead
of returning 4 characters, its only returning 1. Is this a known issue with UDF, and if so, is there anyway around this?
CREATE FUNCTION FinYrDate (@FinYr varchar, @FinYr_Mnth nvarchar)
DECLARE @ReturnedDate datetime
SET @ReturnedDate =
WHEN @FinYr_Mnth < 4 THEN convert(datetime,(cast((@FinYr_Mnth + 3) as varchar) + '-1-' + SUBSTRING(@FinYr,1,4)))
WHEN @FinYr_Mnth between 4 and 9 THEN convert(datetime,(cast((@FinYr_Mnth + 3) as varchar) + '-1-' + SUBSTRING(@FinYr,6,4)))
View Complete Post