[ross_petersen]

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

[Amadeus]

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.

[ross_petersen]

Hi amadeus

I'll give it a go

thanks

Ross

Amadeus, 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.

[ross_petersen]

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.

[gregoryH]

ross_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.

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

[ross_petersen]

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

[gregoryH]

ross_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