February 3, 2006 at 12:46 pm
I am converting an Access Public Function into a UDF in SQL. It is passed a field, which is a datetime field in both Access and SQL. The field could be a 'yyyy-mm' field or a field with '9999 Manual' in it. If the date is in the current month it returns 'CURRENT', if the date is older than four years from the current month, it returns 'PRIOR', if the field is '9999 Manual' then it returns '9999 Manual, if it is NULL then it returns a ''(zero-length string), and otherwise it reformats the date to the 'yyyy-mm' format.
Here is the Access version:
Public Function ReFormatDate(xdate As String) As String
xdate1 = Nz(xdate, "")
If xdate1 = "" Then
ReFormatDate = ""
Exit Function
End If
yyear = Val(Mid(xdate1, 1, 4))
ymonth = Val(Mid(xdate1, 6, 2))
If yyear = "9999" Then
ReFormatDate = xdate1
Exit Function
End If
ydate = CDate(ymonth & "/01/" & yyear)
If CommDate48Month = 0 Then
sDate48 = Format(DateAdd("m", -48, Date$), "mm/01/yyyy")
CommDate48Month = CDate(sDate48)
End If
If CommDate00Month = 0 Then
sDate00 = Format(DateAdd("m", -0, Date$), "mm/01/yyyy")
CommDate00Month = CDate(sDate00)
End If
If ydate = CommDate00Month Then
ReFormatDate = " CURRENT"
Else
If ydate < CommDate48Month Then
ReFormatDate = " PRIOR"
Else
ReFormatDate = Format(ydate, "YYYY-MM")
End If
End If
End Function
Here is the SQL version:
CREATE function ReFormatDate (@date varchar(15), @CurrentDate datetime)
RETURNS varchar(15)
AS
BEGIN
/*Converts date to 'yyyy-mm' format*/
DECLARE @ReformatDate varchar(15)
DECLARE @CommDate00Month As Datetime
DECLARE @CommDate48Month As Datetime
DECLARE @date1 varchar(15)
DECLARE @ydate Datetime
DECLARE @sDate00 Datetime
DECLARE @sDate48 Datetime
DECLARE @ymonth varchar(10)
DECLARE @yyear varchar(10)
SET @date1=ISNULL(@date,'')
IF @date1 = '' BEGIN
SET @ReFormatDate = ''
GOTO ExitFunction
END
SET @yyear = SUBSTRING(@date1, 1, 4)
SET @ymonth = SUBSTRING(@date1, 6, 2)
If @yyear = '9999' BEGIN
SET @ReFormatDate = @date1
GOTO ExitFunction
End
SET @ydate = CAST((@ymonth + '/01/' + @yyear) AS Datetime)
SET @sDate48 = convert(char(10),(DateAdd(month, -48, @CurrentDate)),101)
SET @sDate48 = RIGHT('0' + convert(varchar(2), DatePart(month, @sDate48)), 2) + '/01/' + CAST(year(@sDate48) AS varchar(4))
SET @sDate48 = convert(char(10),@sDate48,101)
SET @sDate00 = convert(char(10),(DateAdd(month, -0, @CurrentDate)),101)
SET @sDate00 = RIGHT('0' + convert(varchar(2), DatePart(month, @sDate00)), 2) + '/01/' + CAST(year(@sDate00) AS varchar(4))
SET @sDate00 = convert(char(10),@sDate00,101)
If @ydate = @sDate00 BEGIN
SET @ReFormatDate = ' CURRENT'
END
Else If @ydate < @sDate48 BEGIN
SET @ReFormatDate = ' PRIOR'
END
Else SET @ReFormatDate = convert(char(07),@ydate,120)
GOTO ExitFunction
ExitFunction:
RETURN @ReformatDate
END
Whenever '9999 Manual' is passed in, I get the error:
Server: Msg 241, Level 16, State 1, Procedure ReFormatDate2, Line 31
Syntax error converting datetime from character string.
which is this line: SET @ydate = CAST((@ymonth + '/01/' + @yyear) AS Datetime)
but, i would think this line would have bypassed that:
If @yyear = '9999' BEGIN
SET @ReFormatDate = @date1
GOTO ExitFunction
End
1.) Can anyone tell me why this error might be happening, or what mistake i made, or
2.) Is there an alternate, better way to handle this?
Thanks so much for any help with this problem.
February 3, 2006 at 1:03 pm
I took your code, unmodified & created the function. This SELECT statement worked fine with no errors:
Select dbo.ReFormatDate('9999 Manual', getdate())
Are you certain you are executing the correct version of the function ? Is it created in the database you expect, and is it owned by 'dbo' ?
February 3, 2006 at 3:52 pm
I agree with PW,
Unless you made a typo you are not executing the same UDF that you posted
CREATE function ReFormatDate (@date varchar(15), @CurrentDate datetime)
Server: Msg 241, Level 16, State 1, Procedure ReFormatDate2, Line 31
February 7, 2006 at 3:27 pm
You're right. I just had some bad test data in there. Duh...Thanks guys.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply