By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,238 Members | 1,518 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,238 IT Pros & Developers. It's quick & easy.

Datadiff

kirubagari
100+
P: 158
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?

Expand|Select|Wrap|Line Numbers
  1. SELECT file_name, org_rcvd_dts,
  2.         DateDiff(hh, bld_start_dts, bld_end_dts)  AS Batch_time, 
  3.         DateDiff(hh, cnv_start_dts, cnv_end_dts)   AS Build_time 
  4. FROM   build_log(nolock) 
  5. WHERE  status <> 'DoNotRerun' 
  6.        AND dist_id = 2 
  7.        AND build_type = 'Full' 
  8. ORDER  BY cnv_start_dts 
Dec 7 '16 #1
Share this Question
Share on Google+
3 Replies


PhilOfWalton
Expert 100+
P: 1,430
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

Expand|Select|Wrap|Line Numbers
  1. Dim Hrs as Integer
  2. Dim Mins as Integer
  3. Dim Secs as Integer
  4.  
  5. SELECT file_name, org_rcvd_dts,
  6.         DateDiff("s", bld_start_dts, bld_end_dts)  AS Batch_time, 
  7.         DateDiff("s", cnv_start_dts, cnv_end_dts)   AS Build_time 
  8. FROM   build_log(nolock) 
  9. WHERE  status <> 'DoNotRerun' 
  10.        AND dist_id = 2 
  11.        AND build_type = 'Full' 
  12. ORDER  BY cnv_start_dts 
  13.  
  14. Hrs = BatchTime \ 3600   ' Note the backslash returns only the whole number of hours
  15. Mins = (BatchTime - (Hrs * 3600)) \60
  16. Sesc - BatchTime - (Hrs * 3600) - (Mins * 60)
  17.  
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
Dec 7 '16 #2

kirubagari
100+
P: 158
Hi Phil,

Thank you. Im using the SQL server and the query is not detected as SQL query.
Expand|Select|Wrap|Line Numbers
  1. Declare @num_hours  Integer
  2. Declare @num_minute as Integer
  3. Declare @num_sec as Integer
  4.  
  5.  
  6. SELECT file_name, org_rcvd_dts,
  7.         DateDiff("s", bld_start_dts, bld_end_dts)  AS Batch_time, 
  8.         DateDiff("s", cnv_start_dts, cnv_end_dts)   AS Build_time 
  9. FROM   build_log(nolock) 
  10. WHERE  status <> 'DoNotRerun' 
  11.        AND dist_id = 2 
  12.        AND build_type = 'Full' 
  13. ORDER  BY cnv_start_dts 
  14.  
Where should i declare the conversation part
Dec 7 '16 #3

PhilOfWalton
Expert 100+
P: 1,430
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
Dec 7 '16 #4

Post your reply

Sign in to post your reply or Sign up for a free account.