Hello people,
Good afternoon!
These last few days I'm a little out of time, but to keep up with updating here I will do another quick post demonstrating how to convert milliseconds, seconds or minutes to TIME in SQL Server.
Often in our routines, especially when we create routine execution logs measuring the time taken to process, we have to store these records in a TIME field or a string that simulates the behavior of the TIME data type (Ex: 00: 01: 08.57800)
However, the conversion, although simple, is not so trivial and raises a lot of doubts, especially in novice SQL Server developers who end up even using functions to do this task and applying it to large volumes of data, impairing query performance. For this reason, I decided to create this post to help with this question.
|
1 2 3 4 5 6 7 8 9 10 11 |
-- Converter 5874502 Milisegundos para tempo SELECT CONVERT(TIME, DATEADD(MILLISECOND, 5874502 + 86400000, 0), 114) -- Converter 587 Segundos para tempo SELECT CONVERT(TIME, DATEADD(SECOND, 587 + 86400000, 0), 114) -- Converter 457 Minutos para tempo SELECT CONVERT(TIME, DATEADD(MINUTE, 457 + 86400000, 0), 114) -- Converter 5874502 Milisegundos para string SELECT CONVERT(VARCHAR(12), DATEADD(MILLISECOND, 5874502 + 86400000, 0), 114) |
Query Result:
To the next!
How to convert milliseconds milliseconds, seconds, or minutes to TIME in SQL Server, convert seconds minutes milliseconds to time
How to convert milliseconds milliseconds, seconds, or minutes to TIME in SQL Server, convert seconds minutes milliseconds to time
Dirceu, good afternoon!
Using DMV-sys.dm_exec_query_stats to validate CPU utilization time for running queries I would like to convert the min_worker_time field that is reported to MICROSECONDS as per the documentation with your script provided above, however, I realize the time for some queries If the CPU time is above acceptable, can I convert microseconds like this as below?
SELECT TOP (10)
SUBSTRING (EST.TEXT, (EQS.statement_start_offset / 2) + 1,
((CASE EQS.statement_end_offset
WHEN -1 THEN DATALENGTH (EST.TEXT)
ELSE EQS.statement_end_offset
END - EQS.statement_start_offset) / 2) + 1) AS Command,
EQS.total_worker_time,
CONVERT (VARCHAR (8), DATEADD (MILLISECOND, EQS.min_worker_time + 86400000, 0), 114) AS CPU_time,
ECP.query_plan
FROM sys.dm_exec_query_stats EQS
CROSS APPLY sys.dm_exec_sql_text (EQS.sql_handle) EST
CROSS APPLY sys.dm_exec_query_plan (EQS.plan_handle) ECP
ORDER BY EQS.total_worker_time DESC
Very Useful Script
Very good
Thank you, Rafael 🙂