Problem with UDF in SQL Server

Error when testing with Query Analyzer

Page 1 of 1

6 Replies - 2793 Views - Last Post: 03 November 2006 - 06:59 AM Rate Topic: -----

#1 ross_petersen  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 65
  • Joined: 06-August 05

Problem with UDF in SQL Server

Post icon  Posted 02 November 2006 - 09:47 AM

Hi folks :)

I have a UDF that goes like this:

CREATE FUNCTION [dbo].[funcGetDateFromFileName] (@filename varchar(50))
RETURNS datetime
AS  
BEGIN 
	DECLARE @extracteddate varchar(50)
	SET @extracteddate = SUBSTRING(@filename, 8, 10)
	RETURN CAST(@extracteddate AS datetime)
END



When I call it in QA as per follows:

SELECT dbo.funcGetDateFromFileName('7HFCFM 16-05-2006_10;20;23_AM.MP3')



I get the following error message:

Server: Msg 242, Level 16, State 3, Procedure funcGetDateFromFileName, Line 7
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.


I have looked at it, but I cannot see where the problem is. The Books Online documentation for CAST says that it SQL Server will do an implicit conversion from VARCHAR to DATETIME.

So, if anybody can help I would very much appreciate it.

Kind regards

Ross

Is This A Good Question/Topic? 0
  • +

Replies To: Problem with UDF in SQL Server

#2 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,506
  • Joined: 12-July 02

Re: Problem with UDF in SQL Server

Posted 02 November 2006 - 09:58 AM

That is peculiar...just for the heck of it, can ou supply it a two digit year using that format? I wonder if it has to do with the expected format? Try with 06 for the year...it may be interesting.
Was This Post Helpful? 0
  • +
  • -

#3 ross_petersen  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 65
  • Joined: 06-August 05

Re: Problem with UDF in SQL Server

Posted 02 November 2006 - 01:13 PM

Hi amadeus

I'll give it a go

thanks

Ross

View PostAmadeus, on 2 Nov, 2006 - 09:58 AM, said:

That is peculiar...just for the heck of it, can ou supply it a two digit year using that format? I wonder if it has to do with the expected format? Try with 06 for the year...it may be interesting.

Was This Post Helpful? 0
  • +
  • -

#4 ross_petersen  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 65
  • Joined: 06-August 05

Re: Problem with UDF in SQL Server

Posted 02 November 2006 - 05:30 PM

Hi amadeus :)

Well I tried your suggestion - here is the modified code for the UDF

CREATE FUNCTION [dbo].[funcGetDateFromFileName] (@filename varchar(50))
RETURNS datetime
AS  
BEGIN 
	DECLARE @extracteddate varchar(50)
	SET @extracteddate = SUBSTRING(@filename, 8, 8)
	RETURN CAST(@extracteddate AS datetime)
END



And here is the code in QA

SELECT dbo.funcGetDateFromFileName('7HFCFM 16-05-06_10;20;23_AM.MP3')



and finally, here is the error message from QA

Server: Msg 242, Level 16, State 3, Procedure funcGetDateFromFileName, Line 7
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.


What do you think?

Bit of a mystery eh. :crazy:
Was This Post Helpful? 0
  • +
  • -

#5 gregoryH  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 60
  • View blog
  • Posts: 656
  • Joined: 04-October 06

Re: Problem with UDF in SQL Server

Posted 02 November 2006 - 11:35 PM

View Postross_petersen, on 2 Nov, 2006 - 05:30 PM, said:

Hi amadeus :)

Well I tried your suggestion - here is the modified code for the UDF

CREATE FUNCTION [dbo].[funcGetDateFromFileName] (@filename varchar(50))
RETURNS datetime
AS  
BEGIN 
	DECLARE @extracteddate varchar(50)
	SET @extracteddate = SUBSTRING(@filename, 8, 8)
	RETURN CAST(@extracteddate AS datetime)
END



And here is the code in QA

SELECT dbo.funcGetDateFromFileName('7HFCFM 16-05-06_10;20;23_AM.MP3')



and finally, here is the error message from QA

Server: Msg 242, Level 16, State 3, Procedure funcGetDateFromFileName, Line 7
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.


What do you think?

Bit of a mystery eh. :crazy:

Hi ross and armadeus

Is there any possibility that the date conversion wants YYYY-MM-DD? I know that is the case for some SQL DB.

regards
Was This Post Helpful? 0
  • +
  • -

#6 ross_petersen  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 65
  • Joined: 06-August 05

Re: Problem with UDF in SQL Server

Post icon  Posted 03 November 2006 - 04:39 AM

Hi Amadeus :)

the problem is fixed - here is the modified UDF

CREATE FUNCTION [dbo].[funcGetDateFromFileName] (@filename varchar(50))
RETURNS datetime
AS  
BEGIN 
	DECLARE @extracteddate varchar(50)
	DECLARE @modifiedfile varchar(50)
	DECLARE @modifieddate varchar(10)
	SET @extracteddate = SUBSTRING(@filename, 8, 10)
	SET @modifieddate = SUBSTRING(@extracteddate,4,2) + '-' + LEFT(@extracteddate, 2) + '-' + RIGHT(@extracteddate,4)
	RETURN CAST(@modifieddate AS datetime)
END



When I run this in QA using the following code:

SELECT vchFileName, dbo.funcGetDateFromFileName(vchFileName) FROM dbo.FileNames



I get the following results:

7HFCFM 16-05-2006_10;20;23_AM.MP3 2006-05-16 00:00:00.000
7HFCFM 16-05-2006_10;24;00_AM.mp3 2006-05-16 00:00:00.000
7HFCFM 16-05-2006_11;22;23_AM.mp3 2006-05-16 00:00:00.000

Being an Aussie, our date format is dd-mm-yyyy rather than mm-dd-yyyy. the key part is the line beginning with SET @modifieddate. That rearranges it so it is looking like US format.

Many thanks for your help

Ross
Was This Post Helpful? 0
  • +
  • -

#7 gregoryH  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 60
  • View blog
  • Posts: 656
  • Joined: 04-October 06

Re: Problem with UDF in SQL Server

Posted 03 November 2006 - 06:59 AM

View Postross_petersen, on 3 Nov, 2006 - 04:39 AM, said:

Hi Amadeus :)

the problem is fixed - here is the modified UDF

CREATE FUNCTION [dbo].[funcGetDateFromFileName] (@filename varchar(50))
RETURNS datetime
AS  
BEGIN 
	DECLARE @extracteddate varchar(50)
	DECLARE @modifiedfile varchar(50)
	DECLARE @modifieddate varchar(10)
	SET @extracteddate = SUBSTRING(@filename, 8, 10)
	SET @modifieddate = SUBSTRING(@extracteddate,4,2) + '-' + LEFT(@extracteddate, 2) + '-' + RIGHT(@extracteddate,4)
	RETURN CAST(@modifieddate AS datetime)
END



When I run this in QA using the following code:

SELECT vchFileName, dbo.funcGetDateFromFileName(vchFileName) FROM dbo.FileNames



I get the following results:

7HFCFM 16-05-2006_10;20;23_AM.MP3 2006-05-16 00:00:00.000
7HFCFM 16-05-2006_10;24;00_AM.mp3 2006-05-16 00:00:00.000
7HFCFM 16-05-2006_11;22;23_AM.mp3 2006-05-16 00:00:00.000

Being an Aussie, our date format is dd-mm-yyyy rather than mm-dd-yyyy. the key part is the line beginning with SET @modifieddate. That rearranges it so it is looking like US format.

Many thanks for your help

Ross

G'day

Throw another prawn on the barby
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1