crompnk
asked on
date function - TSQL
Hi,
I have a function that takes the french date for example '20-févr.-09' and converts it to the english equivalent '20-Feb-09'
The function creates successfully but when I execute it as:
select dbo.FRENCH_DATE('20-févr.- 09')
I get the following error:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '20-févr-09-09' to data type int.
I have tried casting the variables but still get the error.
Thank you.
I have a function that takes the french date for example '20-févr.-09' and converts it to the english equivalent '20-Feb-09'
The function creates successfully but when I execute it as:
select dbo.FRENCH_DATE('20-févr.-
I get the following error:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '20-févr-09-09' to data type int.
I have tried casting the variables but still get the error.
Thank you.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FRENCH_DATE]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[FRENCH_DATE]
go
CREATE FUNCTION [dbo].[FRENCH_DATE] (@date VARCHAR(30))
RETURNS INT
AS
/***********************************************************************
Summary: Converts the french date to english date
Examples: select dbo.FRENCH_DATE('20-févr.-09')
***********************************************************************/
BEGIN
DECLARE @day VARCHAR(2)
DECLARE @month VARCHAR(9)
DECLARE @year VARCHAR(2)
DECLARE @RETURNSTRING VARCHAR(30)
SET @RETURNSTRING = ''
-- subtract day from date string
SET @day = LEFT(CAST(@date AS VARCHAR),2)
-- subtract month from date string
SET @month = REPLACE(SUBSTRING(CAST(@date AS VARCHAR),4,LEN(CAST(@date AS VARCHAR))-3),'.','')
-- subtract year from date string
SET @year = RIGHT(CAST(@date AS VARCHAR),2)
IF @month = 'févr'
SET @RETURNSTRING = CAST(@day AS VARCHAR) + '-Feb-' + CAST(@year AS VARCHAR)
ELSE
SET @RETURNSTRING = CAST(@day AS VARCHAR) + '-' + CAST(@month AS VARCHAR) + '-' + CAST(@year AS VARCHAR)
RETURN @RETURNSTRING
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you are returning @RETURNSTRING variable which is varchar and you define in function defination that you will return INT value which is mismatch. so change RETURNS to VARCHAR from INT as per said in above comment
sorry, I was bit late :)
ASKER
Hi,
I have modified the function, please see script below:
It seems to only work for when @month = 'févr' and not any other date, for example the following select gives no month:
select dbo.AFN_FRENCH_DATE('20-dé cembre-08' )
Thank you
I have modified the function, please see script below:
It seems to only work for when @month = 'févr' and not any other date, for example the following select gives no month:
select dbo.AFN_FRENCH_DATE('20-dé
Thank you
CREATE FUNCTION [dbo].[FRENCH_DATE] (@date VARCHAR(30))
RETURNS varchar(14)
AS
BEGIN
DECLARE @day VARCHAR(2)
DECLARE @month VARCHAR(9)
DECLARE @year VARCHAR(2)
DECLARE @RETURNSTRING VARCHAR(30)
SET @RETURNSTRING = ''
SET @day = LEFT(@date,2)
SET @month = SUBSTRING(REPLACE(@date,'.',''),4,LEN(REPLACE(@date,'.',''))-6)
IF @month = 'janvier' SET @month = 'Jan'
IF @month = 'février' SET @month = 'Feb'
IF @month = 'mars' SET @month = 'Mar'
IF @month = 'avril' SET @month = 'Apr'
IF @month = 'mai' SET @month = 'May'
IF @month = 'juin' SET @month = 'Jun'
IF @month = 'juillet' SET @month = 'Jul'
IF @month = 'août' SET @month = 'Aug'
IF @month = 'septembre' SET @month = 'Sep'
IF @month = 'octobre' SET @month = 'Oct'
IF @month = 'novembre' SET @month = 'Nov'
IF @month = 'décembre' SET @month = 'Dec'
IF @month = 'févr' SET @month = 'Feb'
ELSE SET @month = ''
SET @year = RIGHT(@date,2)
SET @RETURNSTRING = @day + '-' + @month + '-' + @year
RETURN @RETURNSTRING
END
this is your problem:
IF @month = 'févr' SET @month = 'Feb'
ELSE SET @month = ''
this if cause it to work only for fevr
IF @month = 'févr' SET @month = 'Feb'
ELSE SET @month = ''
this if cause it to work only for fevr
P