Solved

date function - TSQL

Posted on 2009-07-15
6
517 Views
Last Modified: 2012-05-07
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.
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

Open in new window

0
Comment
Question by:crompnk
6 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 250 total points
ID: 24859491
replace
RETURNS INT

with
RETURNS varchar(14)
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24859549
Yes, so you are specifying that you are returning a value of INT datatype whereas in your return statement in the end of the fucntion you are returning a varchar value. So as momi_sabaq suggested you have to update your RETURNS satement in the procedure definition.
P
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24859602
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
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 31

Expert Comment

by:RiteshShah
ID: 24859610
sorry, I was bit late :)
0
 

Author Comment

by:crompnk
ID: 24897840
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
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

Open in new window

0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 24899805
this is your problem:

IF @month = 'févr' SET @month = 'Feb'
 
      ELSE SET @month = ''
 


this if cause it to work only for fevr
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 r2 - Varible Table 3 32
Query Help - MSSQL - Averages 5 30
Syntax for query to update table 2 29
SQL - format decimal in a string 5 40
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question