Solved

date function - TSQL

Posted on 2009-07-15
6
514 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
Comment Utility
replace
RETURNS INT

with
RETURNS varchar(14)
0
 
LVL 17

Expert Comment

by:pssandhu
Comment Utility
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
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 31

Expert Comment

by:RiteshShah
Comment Utility
sorry, I was bit late :)
0
 

Author Comment

by:crompnk
Comment Utility
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
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

763 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

12 Experts available now in Live!

Get 1:1 Help Now