Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

help with datediff Expand / Collapse
Author
Message
Posted Tuesday, June 25, 2013 12:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 2:28 AM
Points: 22, Visits: 113
avishain (6/25/2013)
ChrisM@Work (6/24/2013)
Something like the algorithms for obtaining datetime from sysjobhistory run date and run time:

SELECT 
jh.run_date,
jh.run_time,
RunDateTime = x.RunDate + x.RunTime
FROM msdb.dbo.sysjobhistory jh
CROSS APPLY (
SELECT
RunDate = CAST(STR(jh.run_date, 8, 0) AS DATETIME),
RunTime = CAST(STUFF(STUFF(RIGHT('00000'+CAST(jh.run_time AS VARCHAR(8)),6),3,0,':'),6,0,':') AS DATETIME)
) x



thank you very much works like a charm



while trying the other 2 fields i get this errori got 2 fields of start and 2 fields of end and they got the same data type as i mentioned earlier)

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Post #1466999
Posted Tuesday, June 25, 2013 1:03 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, August 25, 2014 5:29 AM
Points: 646, Visits: 1,862
Sounds like a data problem - you'll have to find the problems in your data first, and then decide how to deal with them.

Suggestions

1) Run your script without the 'cast to datetime' i.e. keeping the result as a varchar. Then examine your results, and look for the one(s) that doesn't look right.

2) Have you any nulls in either field?

3) check the len(x) of the fields and look for misfits.

Once you've found the problem data, then you need to judge whether it's enough to correct it, or whether more such data can find it's way in to the DB, in which case you'll have to 'handle it' more gracefully.
Post #1467004
Posted Tuesday, June 25, 2013 4:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 2:28 AM
Points: 22, Visits: 113
Ok after a couple of hours i figures what the problem is.

when the time (hours) is '240000' the query failed.

so i need to replace '240000' with '000000' and everything is fine...


Post #1467068
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse