September 22, 2016 at 6:00 pm
Hello Folks,
How can I convert StartTime2 so that it equals StartTime1
DECLARE @StartTime1 TIME
SET @StartTime1 = '17:41:00.0000000'
SELECT @StartTime1
DECLARE @StartTime2 DATETIME
SET @StartTime2 = '2016-09-22 17:41:14.810'
SELECT @StartTime2
I can convert StartTime2 to time OK
SELECT CONVERT(TIME,@StartTime2)
My desired result is a conversion of @StartTime2 to '17:41:00.0000000' (not 17:41:14.8100000)
This is a variation of the old 'How can I remove the time from DATETIME', instead it is 'How can I remove seconds and milliseconds from TIME'.
September 22, 2016 at 6:23 pm
Something along these lines?
DECLARE @StartTime1 TIME = '17:41:00.0000000';
DECLARE @StartTime2 DATETIME = '2016-09-22 17:41:14.810';
if @StartTime1 = TimeFromParts(datepart(hh,@StartTime2),datepart(n,@starttime2),0,0,0)
select 'Times match'
else
select 'Times do not match';
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 22, 2016 at 6:44 pm
Perfect thank you! I didn't know TimeFromParts existed and it is exactly what I was looking for.
September 23, 2016 at 10:31 am
Here's another option...
DECLARE @StartTime1 TIME = '17:41:00.0000000';
DECLARE @StartTime2 DATETIME = '2016-09-22 17:41:14.810';
if @StartTime1 = CAST(DATEADD(mi, DATEDIFF(mi, 0, @StartTime2), 0) AS TIME)
select 'Times match'
else
select 'Times do not match';
October 5, 2016 at 3:23 pm
SELECT CONVERT(TIME,CONVERT(SMALLDATETIME, @StartTime2))
_____________
Code for TallyGenerator
October 5, 2016 at 3:52 pm
How about just changing the declaration of @StartTime2 to SMALLDATETIME?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 5, 2016 at 4:00 pm
drew.allen (10/5/2016)
How about just changing the declaration of @StartTime2 to SMALLDATETIME?Drew
How about not splitting date and time for StartDateTime?
😀
_____________
Code for TallyGenerator
October 5, 2016 at 7:38 pm
Sergiy (10/5/2016)
drew.allen (10/5/2016)
How about just changing the declaration of @StartTime2 to SMALLDATETIME?Drew
How about not splitting date and time for StartDateTime?
😀
+1000 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
October 10, 2016 at 12:06 pm
My use case is I have daily jobs that are scheduled to run at a specific time. I want to alert when a job did not run. I thought TIME would be the most appropriate data type for the JobStartTime field since the field determines the expected daily start time for the job and there really is not a date associated with the expected start time.
DECLARE @Jobs TABLE
(
JobName VARCHAR(10),
JobStartTime TIME
)
INSERT INTO @Jobs (JobName,JobStartTime) VALUES ('Job1','07:05:00.0000')
INSERT INTO @Jobs (JobName,JobStartTime) VALUES ('Job2','08:05:00.0000')
SELECT * FROM @Jobs
DECLARE @Logs TABLE
(
JobName VARCHAR(10),
JobStartDateTime DATETIME
)
INSERT INTO @Logs (JobName,JobStartDateTime) VALUES ('Job1','2016-10-10 07:05:00.000')
SELECT
J.JobName,
JobStartDateTime,
CASE WHEN L.JobStartDateTime IS NOT NULL THEN 'Success' ELSE 'Failure' END
AS JobDailyRunStatus
FROM @Jobs J
LEFT JOIN @Logs L
ON J.JobName = L.JobName AND JobStartTime = TIMEFROMPARTS(datepart(hh,JobStartDateTime),datepart(n,JobStartDateTime),0,0,0)
October 10, 2016 at 12:48 pm
Chrissy321 (10/10/2016)
My use case is I have daily jobs that are scheduled to run at a specific time. I want to alert when a job did not run.
Your description does not match the query. This query will tell you that a job did not run even if it has. For instance, if a job runs early, you'll get a failure; if a job runs a little late, you'll get a failure. The jobs have run, they just haven't run within a specific time from their scheduled start time. Do you really need to know that they didn't run within a specific time frame, or only that they didn't run at all?
Also, you're not testing for whether the job has reached it's scheduled kickoff time. If this query is for today's jobs, do you really want an alert that a job has failed to run even though it's not yet scheduled to run?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 10, 2016 at 1:06 pm
drew.allen (10/10/2016)
Do you really need to know that they didn't run within a specific time frame, or only that they didn't run at all?Drew
The former.
drew.allen (10/10/2016)
Also, you're not testing for whether the job has reached it's scheduled kickoff time. If this query is for today's jobs, do you really want an alert that a job has failed to run even though it's not yet scheduled to run?Drew
Yes I do need to check if the job has reached its scheduled kickoff time.
WHERE JobStartTime < CONVERT(TIME,GETDATE())
October 10, 2016 at 2:02 pm
Chrissy321 (10/10/2016)
My use case is I have daily jobs that are scheduled to run at a specific time. I want to alert when a job did not run. I thought TIME would be the most appropriate data type for the JobStartTime field since the field determines the expected daily start time for the job and there really is not a date associated with the expected start time.DECLARE @Jobs TABLE
(
JobName VARCHAR(10),
JobStartTime TIME
)
INSERT INTO @Jobs (JobName,JobStartTime) VALUES ('Job1','07:05:00.0000')
INSERT INTO @Jobs (JobName,JobStartTime) VALUES ('Job2','08:05:00.0000')
SELECT * FROM @Jobs
DECLARE @Logs TABLE
(
JobName VARCHAR(10),
JobStartDateTime DATETIME
)
INSERT INTO @Logs (JobName,JobStartDateTime) VALUES ('Job1','2016-10-10 07:05:00.000')
SELECT
J.JobName,
JobStartDateTime,
CASE WHEN L.JobStartDateTime IS NOT NULL THEN 'Success' ELSE 'Failure' END
AS JobDailyRunStatus
FROM @Jobs J
LEFT JOIN @Logs L
ON J.JobName = L.JobName AND JobStartTime = TIMEFROMPARTS(datepart(hh,JobStartDateTime),datepart(n,JobStartDateTime),0,0,0)
1. Your use case is so limited that it's not worth any development time to be spent on it.
2. Job started at the time does not mean it's completed successfully.
Job started next minute does not mean it's failed.
3. It's enough for a job to be started exactly at scheduled time once to be rooted as "Success" forever.
Since you do not store the date in Log a record from 2 years ago will perfectly match the LEFT JOIN condition, and your query will report success, even if the job has been deleted a year ago.
_____________
Code for TallyGenerator
October 10, 2016 at 9:51 pm
Chrissy321 (10/10/2016)
Yes I do need to check if the job has reached its scheduled kickoff time.WHERE JobStartTime < CONVERT(TIME,GETDATE())
For a job scheduled to start on 23:59 this condition will never be fulfilled.
_____________
Code for TallyGenerator
October 11, 2016 at 3:55 pm
Sergiy (10/5/2016)
SELECT CONVERT(TIME,CONVERT(SMALLDATETIME, @StartTime2))
Please be aware that when converting a DATETIME to a SMALLDATETIME that when the seconds are <30 the minutes will match but when the seconds are 30+ then the minutes will be rounded up.
When @StartTime2 = 2016-10-11 15:48:29.372 the result will be 15:48:00.0000000.
When @StartTime2 = 2016-10-11 15:48:31.283 the result will be 15:49:00.0000000.
October 12, 2016 at 2:07 pm
If all you are interested in comparing is the time, you can easily compare the sysjobschedules.active_start_time with sysjobhistory.run_time. They are in the same format (integer) so a simple equality comparison will tell you if the job (step 0) started in the same second it was expected to start. Divide them by 100 to truncate the seconds and compare minutes.
This doesn't filter the jobs by schedule, but you can easily see the Minutes comparison from the schedule vs last run time. (The schedule time is when the interval frequency is to start, so running every hour starting at 4am would show a schedule start time of 40000). You can extend this to get to your goal.
WITH cte
AS ( SELECT JobName = j.name ,
ScheduleName = sc.name ,
ScheduledTime = CAST(dbo.agent_datetime(19000101, sc.active_start_time) AS TIME(0)) ,
RunTime = CAST(dbo.agent_datetime(19000101, sjh.run_time) AS TIME(0)) ,
sc.active_start_time ,
sjh.run_time ,
RunSeq = ROW_NUMBER() OVER ( PARTITION BY j.job_id ORDER BY sc.active_start_date DESC, sc.active_start_time DESC )
FROM sysjobs AS j
INNER JOIN dbo.sysjobschedules AS sj
ON sj.job_id = j.job_id
INNER JOIN dbo.sysschedules AS sc
ON sc.schedule_id = sj.schedule_id
LEFT OUTER JOIN dbo.sysjobhistory AS sjh
ON sjh.job_id = j.job_id
AND sjh.step_id = 0
)
SELECT JobName ,
ScheduledTime ,
RunTime ,
ScheduleName ,
ScheduleTimeMinutes = cte.active_start_time / 100 ,
ActualStartMintes = cte.run_time / 100
FROM cte
WHERE cte.RunSeq = 1;
Wes
(A solid design is always preferable to a creative workaround)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply