help with datediff

  • hello there

    got 2 time fields

    *start date

    *end date

    the format of these columns is hhmmss

    i want to calculate the duration . (end date-start date)

    could i do this with datdiff or any other function?

    pay attention that a situation like this can be happened :

    *start date 235641

    *end date 001612

    so if you subtract ( 001612-235641) you got a negative number , in that case, i think you need to add 24 hours...

    thank you

  • do you have any other columns available that hold date info ...eg yyymmdd?

    also what are you expecting to happen when the end time is two or more days after the start time

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • While you're investigating JLS' questions, this is worth playing with:

    SELECT

    [DTstart date],

    [DTend date],

    SecondsDifference = DATEDIFF(second,[DTend date],[DTstart date]),

    SecondsDifference = ABS(DATEDIFF(second,[DTstart date],[DTend date]))

    FROM (

    SELECT

    [DTstart date] = CONVERT(DATETIME,STUFF(STUFF(CAST(d.[start date] AS VARCHAR(8)),3,0,':'),6,0,':'),114),

    [DTend date] = CONVERT(DATETIME,STUFF(STUFF(CAST(d.[end date] AS VARCHAR(8)),3,0,':'),6,0,':'),114)

    FROM (

    SELECT [start date] = '235641', [end date] = '001612'

    ) d

    ) e

    โ€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.โ€ - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • J Livingston SQL (6/24/2013)


    do you have any other columns available that hold date info ...eg yyymmdd?

    also what are you expecting to happen when the end time is two or more days after the start time

    I DO HAVE ANOTHER COLUMNS

    Date_StartHour_StartDate_EndHour_End

    2011062110423220110621121805

    as you can see, i got 2 columns referring to the date and 2 columns referring to the time (exact hour)

    the date format is yyyymmdd

    the time format is hhmmss

    thank you for helping ๐Ÿ™‚

  • avishain (6/24/2013)


    J Livingston SQL (6/24/2013)


    do you have any other columns available that hold date info ...eg yyymmdd?

    also what are you expecting to happen when the end time is two or more days after the start time

    I DO HAVE ANOTHER COLUMNS

    Date_StartHour_StartDate_EndHour_End

    2011062110423220110621121805

    as you can see, i got 2 columns referring to the date and 2 columns referring to the time (exact hour)

    the date format is yyyymmdd

    the time format is hhmmss

    thank you for helping ๐Ÿ™‚

    What datatype are the columns?

    โ€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.โ€ - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • ChrisM@Work (6/24/2013)


    avishain (6/24/2013)


    J Livingston SQL (6/24/2013)


    do you have any other columns available that hold date info ...eg yyymmdd?

    also what are you expecting to happen when the end time is two or more days after the start time

    I DO HAVE ANOTHER COLUMNS

    Date_StartHour_StartDate_EndHour_End

    2011062110423220110621121805

    as you can see, i got 2 columns referring to the date and 2 columns referring to the time (exact hour)

    the date format is yyyymmdd

    the time format is hhmmss

    thank you for helping ๐Ÿ™‚

    What datatype are the columns?

    varchar (8) (date) and varchar(6) (hour)

  • You're probably going to have to cut up the time and insert colons, using the substring (and if you like, left and right) functions, so that you end up with something you can cast to a date, at which point you can use the datediff function as intended.

    DECLARE @mydate VARCHAR(40)

    SELECT @mydate='20110621 10:42:32'

    SELECT CAST(@mydate AS DATETIME)

  • ....something like....

    cast(date_start + left(hour_start,2)+':'+substring (hour_start,3,2)+':'+right(hour_start,2) as datetime)

  • Something like the algorithms for obtaining datetime from sysjobhistory run date and run time:

    SELECT

    jh.run_date,

    jh.run_time,

    RunDateTime = x.RunDate + x.RunTime

    FROM msdb.dbo.sysjobhistory jh

    CROSS APPLY (

    SELECT

    RunDate = CAST(STR(jh.run_date, 8, 0) AS DATETIME),

    RunTime = CAST(STUFF(STUFF(RIGHT('00000'+CAST(jh.run_time AS VARCHAR(8)),6),3,0,':'),6,0,':') AS DATETIME)

    ) x

    โ€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.โ€ - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • ChrisM@Work (6/24/2013)


    Something like the algorithms for obtaining datetime from sysjobhistory run date and run time:

    SELECT

    jh.run_date,

    jh.run_time,

    RunDateTime = x.RunDate + x.RunTime

    FROM msdb.dbo.sysjobhistory jh

    CROSS APPLY (

    SELECT

    RunDate = CAST(STR(jh.run_date, 8, 0) AS DATETIME),

    RunTime = CAST(STUFF(STUFF(RIGHT('00000'+CAST(jh.run_time AS VARCHAR(8)),6),3,0,':'),6,0,':') AS DATETIME)

    ) x

    thank you very much works like a charm

  • avishain (6/25/2013)


    ChrisM@Work (6/24/2013)


    Something like the algorithms for obtaining datetime from sysjobhistory run date and run time:

    SELECT

    jh.run_date,

    jh.run_time,

    RunDateTime = x.RunDate + x.RunTime

    FROM msdb.dbo.sysjobhistory jh

    CROSS APPLY (

    SELECT

    RunDate = CAST(STR(jh.run_date, 8, 0) AS DATETIME),

    RunTime = CAST(STUFF(STUFF(RIGHT('00000'+CAST(jh.run_time AS VARCHAR(8)),6),3,0,':'),6,0,':') AS DATETIME)

    ) x

    thank you very much works like a charm

    while trying the other 2 fields i get this error:(i got 2 fields of start and 2 fields of end and they got the same data type as i mentioned earlier)

    Msg 242, Level 16, State 3, Line 1

    The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

  • Sounds like a data problem - you'll have to find the problems in your data first, and then decide how to deal with them.

    Suggestions

    1) Run your script without the 'cast to datetime' i.e. keeping the result as a varchar. Then examine your results, and look for the one(s) that doesn't look right.

    2) Have you any nulls in either field?

    3) check the len(x) of the fields and look for misfits.

    Once you've found the problem data, then you need to judge whether it's enough to correct it, or whether more such data can find it's way in to the DB, in which case you'll have to 'handle it' more gracefully.

  • Ok after a couple of hours i figures what the problem is.

    when the time (hours) is '240000' the query failed.

    so i need to replace '240000' with '000000' and everything is fine...

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply