question

sqldev6 avatar image
sqldev6 asked

How to convert datetime to only time

select SERVER, stop_execution_date from msdb..sysjobactivity inner join msdb..sysjobhistory on msdb..sysjobactivity.job_id = msdb..sysjobhistory.job_id where stop_execution_date > This is the query I am working with. stop_execution_date is "datetime" datatype. I need to get details of job which runs after certain time. How to do it?
sqlsql serverdatetimesql 2008datatype
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
JohnM avatar image
JohnM answered
Let's try this again. My first answer was incorrect because I misread the question. ;-) You'll probably want to look at the CAST/CONVERT functions. Here is an example of both: SELECT SERVER , CAST(stop_execution_date AS DATE) AS TheDate, CAST(stop_execution_date AS TIME) AS CastTheTime, CONVERT(TIME,stop_execution_date) AS ConvertTheTime, stop_execution_date FROM msdb..sysjobactivity INNER JOIN msdb..sysjobhistory ON msdb..sysjobactivity.job_id = msdb..sysjobhistory.job_id WHERE CAST(stop_execution_date AS DATE) >= '2014-07-14' AND CAST(stop_execution_date AS TIME) >= '05:00:00' I included the addition filter in the where clause to just narrow down the results on my test server and to confirm that the code was working as I expected. You could also just adjust the where clause in your original query to reflect the appropriate time: SELECT SERVER , stop_execution_date FROM msdb..sysjobactivity INNER JOIN msdb..sysjobhistory ON msdb..sysjobactivity.job_id = msdb..sysjobhistory.job_id WHERE stop_execution_date > '2014-07-14 14:00:02' Keeping in mind that if you want that time (2:00:02PM) to be included you'll need to use the '>=' operator. Hope this helps!
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.