CONVERT DATETIME to TIME and remove seconds and milliseconds

  • 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'.

  • 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';

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Perfect thank you! I didn't know TimeFromParts existed and it is exactly what I was looking for.

  • 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';

  • SELECT CONVERT(TIME,CONVERT(SMALLDATETIME, @StartTime2))

    _____________
    Code for TallyGenerator

  • How about just changing the declaration of @StartTime2 to SMALLDATETIME?

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • 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

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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)

  • 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

  • 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())

  • 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

  • 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

  • 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.

  • 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