designsopf.blogg.se

If you must produce formatted output directly from SQL rather than in the front end then your approach is reasonable, however, there are some unecessary modulo operations in your expressions, e.g. Description It's easy to do date arithmetic in Oracle Database, but it can be hard to remember the formulas. Also, the time difference calculation will overflow if the difference is greater than about 24.8 days (when number of milliseconds = 2e31).DECLARE datetimeDECLARE datetimeSELECT = '20090316 12:00:00.000', = GETDATESELECT CONVERT(varchar(12), DATEADD(ms, DATEDIFF(ms, ), 0), 114). It will work fine if the time difference is guaranteed to be less than 24 hours.However, if the time difference is greater than 24 hours, it will not give you what you want as it will display the number of hours modulo 24. This will return the time difference including milliseconds.

Does anyone have any ideas?Thanks!Michael. I have searched and have not found anything. I have the following T-SQL code to change the difference between datetime columns into hours, minutes, and secondsCONVERT(varchar(6), DATEDIFF(second, b.DateTimeStamp, c.DateTimeStamp)/3600) + ':' + RIGHT('0' + CONVERT(varchar(2), (DATEDIFF(second, b.DateTimeStamp, c.DateTimeStamp)% 3600) / 60), 2) + ':' + RIGHT('0' + CONVERT(varchar(2), DATEDIFF(second, b.DateTimeStamp, c.DateTimeStamp)% 60), 2)but I also wanted milliseconds.