Datadiff

[kirubagari]

Hi ,

I would like to convert the datadiff to hours,minutes and second and total up batch time and build time in different column . Can expert show how this can be done in below query?

SELECT file_name, org_rcvd_dts,
        DateDiff(hh, bld_start_dts, bld_end_dts)  AS Batch_time, 
        DateDiff(hh, cnv_start_dts, cnv_end_dts)   AS Build_time 
FROM   build_log(nolock) 
WHERE  status <> 'DoNotRerun' 
       AND dist_id = 2 
       AND build_type = 'Full' 
ORDER  BY cnv_start_dts 

[PhilOfWalton]

Basically you need to get the DateDiff in seconds, not hours as in your code. Then convert the seconds to hours, minutes and seconds.

So Try

Dim Hrs as Integer
Dim Mins as Integer
Dim Secs as Integer
 
SELECT file_name, org_rcvd_dts,
        DateDiff("s", bld_start_dts, bld_end_dts)  AS Batch_time, 
        DateDiff("s", cnv_start_dts, cnv_end_dts)   AS Build_time 
FROM   build_log(nolock) 
WHERE  status <> 'DoNotRerun' 
       AND dist_id = 2 
       AND build_type = 'Full' 
ORDER  BY cnv_start_dts 
 
Hrs = BatchTime \ 3600   ' Note the backslash returns only the whole number of hours
Mins = (BatchTime - (Hrs * 3600)) \60
Sesc - BatchTime - (Hrs * 3600) - (Mins * 60)
 

Note that with the DateDiff function, the time unit is a string and needs to be surrounded with quotes. I doubt that your function with hh and no quotes would have worked.

Phil

[kirubagari]

Hi Phil,

Thank you. Im using the SQL server and the query is not detected as SQL query.

Declare @num_hours  Integer
Declare @num_minute as Integer
Declare @num_sec as Integer
 
 
SELECT file_name, org_rcvd_dts,
        DateDiff("s", bld_start_dts, bld_end_dts)  AS Batch_time, 
        DateDiff("s", cnv_start_dts, cnv_end_dts)   AS Build_time 
FROM   build_log(nolock) 
WHERE  status <> 'DoNotRerun' 
       AND dist_id = 2 
       AND build_type = 'Full' 
ORDER  BY cnv_start_dts 
 

Where should i declare the conversation part

[PhilOfWalton]

Whoops, sorry. I was thinking it was Access VBA. Don't know anything about SQL server, so I can't help anymore. I think, providing you can get your query to return the seconds, the conversion to hours, minutes & seconds should work

Again, apologies.

Phil