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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy