Solved

date function - TSQL

Posted on 2009-07-15
6
515 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Restrict result set 1 35
Help with SQL - TOP 10 by date and by group 13 34
MS SQL - Rotating Values in SQL 9 55
Optimizing a query 3 34
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…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

863 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now