Convert Numeric to Date Function error - out of range time value

[sara07]

Hello, I have a table where I am trying to select the data using this function dbo.numeric_to_date(Created_Date,Created_Time) from table ....

The created_Date(int) and Created_Time ( smallint) are integer values.

And it stores the data as Created_Date: 20080519
Created_Time: 1660

The problem is where I noticed that whenever the Created_Time column has last two numbers with 60(seconds) it throws an error. I think it shuold automatically round it to 1700 but it doesnot.

My function is like this:


CREATE function [dbo].[numeric_to_date](@numeric_date numeric(9), @smallint_time smallint)
returns datetime
as
begin

declare @return_value datetime

---------------------------------------------
Note: I added this piece of code later to fix the problem. but this did not fix I think since there are many records in the table and this only fixes one record at a time.. i am not sure..

if @smallint_time like '%60'
begin
set @smallint_time = round(@smallint_time, -2)
end
----------------------------------------------

if @numeric_date = 0
begin
select @return_value = null
end
else
begin
if @smallint_time = 0 or @smallint_time = null
begin
select @return_value = convert(datetime,substring(convert(varchar(8),convert(numeric(9),@numeric_date)),5,2) + '/'
+ substring(convert(varchar(8),convert(numeric(9),@numeric_date)),7,2) + '/'
+ substring(convert(varchar(8),convert(numeric(9),@numeric_date)),1,4))
end
else
begin
select @return_value = convert(datetime,ltrim(rtrim(substring(convert(varchar(8),convert(numeric(9),@numeric_date)),5,2) + '/'
+ substring(convert(varchar(8),convert(numeric(9),@numeric_date)),7,2) + '/'
+ substring(convert(varchar(8),convert(numeric(9),@numeric_date)),1,4)) + ' '
+ substring(convert(varchar(4),right('0000' + convert(varchar(4),@smallint_time),4)),1,2) + ':'
+ substring(convert(varchar(4),right('0000' + convert(varchar(4),@smallint_time),4)),3,2)))

end
end


return(@return_value)
end


How Can I fix this function so that it rounds if created_time is 1660 or when it has 60 seconds then round it otherwise Dont.. Please Help

Thanks

 

[ousoonerjoe]

Question:

Is the following supposed to be true?

1660 = 1700 = 5 PM

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[genomon]

Sorry I haven't time to run thru all this in detail; can't you just concatenate the values & CAST as DATETIME?
I'll check back in tomorrow....

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[sara07]

Hi,

I beleive 1660 is 4:60 which shouldnt be right. It should round to 5 o clock. Not stay as 460 I beleive thats why it craps out.

 

[ousoonerjoe]

Quickly going over the statement, I don't see where there's a check to see if the minutes/seconds come to 60 and increment accordingly.

A simple test of genomon's suggestion showed(I thought that would have worked too):

SELECT CAST('14:60' AS DATETIME) 

[COLOR=red]Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.[/color]

SELECT CAST('14:59' AS DATETIME)
[COLOR=blue]1900-01-01 14:59:00.000[/color]

[COLOR=green]--both of the following generated the same error listed below:[/color]
SELECT CAST('1460' AS DATETIME)
SELECT CAST('1459' AS DATETIME)

[COLOR=red]Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.[/color]

Simply CASTing won't solve it. You will need to test for '60' and adjust accordingly.


"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[SQLSister]

I spent a long long time working out a solution for this and thought I posted it but somehow it isn't here. Unfrortunately I didn;t save it and today I don;t have the time to recreate what I did. Let me tell you some of waht you have to consider though. First the best method I found is to mathmatically calculate the number of minutes and then divide by 60 and round to just the total hours, then add 00 to the end (all this in a case statment to only do this work on records which end in 60). then you have to account for what you want to do when the time is 2360. That should rollover to 0000, but if it does, does the date filed also need to be updated? You may also need some intenal datatype conversion depending on the datatype the time is in. Then if you want to add a zero infronot of times that are less than 1000, you may need to do that.

YOu also need to consider what to do if someone puts in a completely invalid time. What will you do with 2589 as a value for instance?

Your best bet actually is to redesign the system to correctly use datetime fields instead of this cobbled up mess. If you can't do that, fix the data as a onetime deal and then force the user application to check for an only accept valid times. Then you won't have to do all this slow conversion stuff in order to run reports.

"NOTHING is more important in a database than integrity." ESquared