sqlquery29 (11/26/2014)
Hi Sean,I tried writing the function
CREATE FUNCTION dbo.Convert_Date (@date_column decimal(7,0))
Returns date as begin
declare @date date
set @date = CONVERT(DATE,CASE WHEN LEFT(@date_column,1) = '1' THEN '20'
ELSE '19' END + RIGHT(@date_column,6), 112)
RETURN @date
i am getting error :
Incorrect syntax near '@date'.
And again this conversion is failing for some date columns, as there are some values '0' in those columns.
Error :Conversion failed when converting date and/or time from character string.
I wanted to make it generic, Please help with this code.
I am new to this coding.
Thanks..
The Issue you have is that you are converting the Input date to a DECIMAL, I thought the original format was a Database VARCHAR/NVARCHAR, so the String manipulations will possibly fail, change the function so that the @date_column input is a VARCHAR/NVARCHAR (I cant remember the exact conversion from DB2 data types to SQL).
/*
AS DECIMAL
*/
DECLARE @date_column DECIMAL(7,0) = '0011201'
declare @date date
set @date = CONVERT(DATE,CASE WHEN LEFT(@date_column,1) = '1' THEN '20'
ELSE '19' END + RIGHT(@date_column,6), 112)
/*
AS VARCHAR
*/
DECLARE @date_column VARCHAR(10) = '0011201'
declare @date date
set @date = CONVERT(DATE,CASE WHEN LEFT(@date_column,1) = '1' THEN '20'
ELSE '19' END + RIGHT(@date_column,6), 112)
GO
/*
--EG
CREATE FUNCTION dbo.Convert_Date (@date_column VARCHAR(20))
Returns date as begin
declare @date date
set @date = CONVERT(DATE,CASE WHEN LEFT(@date_column,1) = '1' THEN '20'
ELSE '19' END + RIGHT(@date_column,6), 112)
RETURN @date
END
*/
Above this demonstrates what your function is trying to do, as a NUMERIC if fails with a conversion error, with a VARCHAR it works.
_________________________________________________________________________
SSC Guide to Posting and Best Practices