February 25, 2010 at 1:45 am
Hi,
When i run the following query i am getting The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
DECLARE @threshholdMinutes int
SET @threshholdMinutes = 5
SELECT JCEXEHOST as HostName,
JCJOBNBR as JobNumber,
JCUSER as E1User,
JCFNDFUF2 as UBE
FROM ps811.svm811.f986110
WHERE jcjobsts='P'
AND JCUSER NOT IN ('SCHEDULER')
AND dateadd(mi, -@threshholdMinutes, getdate())>
cast(
cast(1900+cast(((JCACTDATE-(JCACTDATE%1000))/1000) as int) as varchar(4))
+ ' ' + substring(cast (JCACTTIME as varchar(6)), 1, 2)
+ ':' + substring(cast (JCACTTIME as varchar(6)), 3, 2)
+ ':' + substring(cast (JCACTTIME as varchar(6)), 5, 2)
as datetime)
+ dateadd(dy, JCACTDATE%1000-1, 0)
In the above query JCACTDATE contains julian dates (110054,110055 ..) which is an integer data type and JCACTTIME contains HHMMSS in 24 hour format which is an integer data type.
When i splitted the JCACTTIME value to HH:MM:SS and converting it to date data type i am getting the conversion failed error mesaage.
Can some body pls assist me to resolve this issue?
Thank you,
Venu Yelchri
February 25, 2010 at 2:34 am
I tried running the same code as
declare @jcactdate as int,@jcactime as int
select @jcactdate=110054,@jcactime=231111
select
cast(
cast(1900+cast(((@jcactdate-(@jcactdate%1000))/1000) as int) as varchar(4))
+ ' ' + substring(cast (@jcactime as varchar(6)), 1, 2)
+ ':' + substring(cast (@jcactime as varchar(6)), 3, 2)
+ ':' + substring(cast (@jcactime as varchar(6)), 5, 2)
as datetime)
+ dateadd(dy, @jcactdate%1000-1, 0)
And it works and gives the output
2010-02-23 23:11:11.000
There was no conversion error. Well i think you might be running the query on some high value of jcactdate which takes your result out of the datetime range.
--Divya
February 25, 2010 at 3:04 am
Thank you for Quick turn around..
After your reply i checked with all the data. JCACTIME column contain some invaild records due to this i am facing issue.
I am gr8full to you.
Thank you,
Venu Yelchri.
February 25, 2010 at 4:01 am
Most welcome.
--Divya
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply