Data Type Convertion Error

Hello,
I need to convert one character field to datetime and after make the
differenc between two fields.
The problem is that if in the output result appears one value that is
biggest then 23:59:59 hours its shown the
following error:
(xxxxxxx row(s) affected)
Server: Msg 242, Level 16, state 3, line 1
The convertion of a char data type to a datetime data type resulted in an
out-of-range datetime value
Example:
select convert(datetime, field, 108) from table_name
or
select convert (datetime, '24:00:00', 108) from table_name
(xxxxxxx row(s) affected)
Server: Msg 242, Level 16, state 3, line 1
The convertion of a char data type to a datetime data type resulted in an
out-of-range datetime value
I need to use all the results and know the difference between them in hour
or minutes.
Example:
select datediff( hh, convert(datetime, field_A, 108), convert(datetime,
field_B, 108))
from table_name
Can you help me?
Thanks and best regardsHi
24:00:00 is not a valid time. In effect, it is 00:00:00.000 the next day.
00:00:00.000 to 23:59:59.999 is
You need to fix your data to a ISO valid date value.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:CD38A959-9022-4BA9-80B6-1AFCDE0AD8FB@.microsoft.com...
> Hello,
> I need to convert one character field to datetime and after make the
> differenc between two fields.
> The problem is that if in the output result appears one value that is
> biggest then 23:59:59 hours its shown the
> following error:
> (xxxxxxx row(s) affected)
> Server: Msg 242, Level 16, state 3, line 1
> The convertion of a char data type to a datetime data type resulted in an
> out-of-range datetime value
> Example:
> select convert(datetime, field, 108) from table_name
> or
> select convert (datetime, '24:00:00', 108) from table_name
>
> (xxxxxxx row(s) affected)
> Server: Msg 242, Level 16, state 3, line 1
> The convertion of a char data type to a datetime data type resulted in an
> out-of-range datetime value
> I need to use all the results and know the difference between them in hour
> or minutes.
> Example:
> select datediff( hh, convert(datetime, field_A, 108), convert(datetime,
> field_B, 108))
> from table_name
> Can you help me?
> Thanks and best regards|||try...
select datediff(mi,
dateadd(ss,cast(substring(field1,1,2) as
int)*60*60+cast(substring(field1,4,2) as int)*60+ cast(substring(field1,7,2)
as int),cast(0 as datetime)),
dateadd(ss,cast(substring(field2,1,2) as
int)*60*60+cast(substring(field2,4,2) as int)*60+ cast(substring(field2,7,2)
as int),cast(0 as datetime))
)
-- example
select datediff(mi,
dateadd(ss,cast(substring('34:15:10',1,2
) as
int)*60*60+cast(substring('34:15:10',4,2
) as int)*60+
cast(substring('34:15:10',7,2) as int),cast(0 as datetime)),
dateadd(ss,cast(substring('24:18:10',1,2
) as
int)*60*60+cast(substring('24:18:10',4,2
) as int)*60+
cast(substring('24:18:10',7,2) as int),cast(0 as datetime))
)
"CC&JM" wrote:

> Hello,
> I need to convert one character field to datetime and after make the
> differenc between two fields.
> The problem is that if in the output result appears one value that is
> biggest then 23:59:59 hours its shown the
> following error:
> (xxxxxxx row(s) affected)
> Server: Msg 242, Level 16, state 3, line 1
> The convertion of a char data type to a datetime data type resulted in an
> out-of-range datetime value
> Example:
> select convert(datetime, field, 108) from table_name
> or
> select convert (datetime, '24:00:00', 108) from table_name
>
> (xxxxxxx row(s) affected)
> Server: Msg 242, Level 16, state 3, line 1
> The convertion of a char data type to a datetime data type resulted in an
> out-of-range datetime value
> I need to use all the results and know the difference between them in hour
> or minutes.
> Example:
> select datediff( hh, convert(datetime, field_A, 108), convert(datetime,
> field_B, 108))
> from table_name
> Can you help me?
> Thanks and best regards|||Thanks everybody.
Best regards
"Aleksandar Grbic" wrote:
[vbcol=seagreen]
> try...
> select datediff(mi,
> dateadd(ss,cast(substring(field1,1,2) as
> int)*60*60+cast(substring(field1,4,2) as int)*60+ cast(substring(field1,7,
2)
> as int),cast(0 as datetime)),
> dateadd(ss,cast(substring(field2,1,2) as
> int)*60*60+cast(substring(field2,4,2) as int)*60+ cast(substring(field2,7,
2)
> as int),cast(0 as datetime))
> )
>
>
> -- example
> select datediff(mi,
> dateadd(ss,cast(substring('34:15:10',1,2
) as
> int)*60*60+cast(substring('34:15:10',4,2
) as int)*60+
> cast(substring('34:15:10',7,2) as int),cast(0 as datetime)),
> dateadd(ss,cast(substring('24:18:10',1,2
) as
> int)*60*60+cast(substring('24:18:10',4,2
) as int)*60+
> cast(substring('24:18:10',7,2) as int),cast(0 as datetime))
> )
>
>
> "CC&JM" wrote:
>