Struggling with time!

  • Unlike the MAC household I am working weekends... but this one deserves some further input.

    Working with dates and times is straight-forward but what I am reading is the date/time data is VarChar. Please specify the format or you are going to get sunk..

    So CAST(..) is fine but my 2c worth is it is going to get you into trouble - as you already noticed with the AM/PM problem.

    Instead use CONVERT(DateTime, @DateVar, 106) for dates and CONVERT(DateTime, @DateVar, 108) for the time part; join them together and work with them as variables. You can choose whichever date/time format you are comfortable with but because you're converting from Var to Date be explicit.

    Your Code:

    SELECT [ARRIVAL_DATE] AS ArriveDate

    ,[ARRIVAL_TIME] AS ArriveTime

    ,LEFT([INITIAL_ASSESSMENT_TIME],2) + ':' + RIGHT([INITIAL_ASSESSMENT_TIME],2) AS AssessTime

    ,DATEDIFF (mi, [ARRIVAL_TIME], AssessTime) AS AssessWait

    ,LEFT([TIME_SEEN_FOR_TREATMENT],2) + ':' + RIGHT([TIME_SEEN_FOR_TREATMENT],2) AS StartTreatTime

    ,DATEDIFF (mi, AssessTime, StartTreatTime) AS TreatWait

    ,LEFT([ATTENDANCE_CONCLUSION_TIME],2) + ':' + RIGHT([ATTENDANCE_CONCLUSION_TIME],2) AS EndTreatTime

    ,DATEDIFF (mi, StartTreatTime, EndTreatTime) AS TreatTime

    ,[DEPARTURE_TIME]

    Here I would definately specify the format of your time (and the same applies to any other vars you manipulate from Vat to date/time.

    Example: LEFT([INITIAL_ASSESSMENT_TIME],2) + ':' + RIGHT([INITIAL_ASSESSMENT_TIME],2)

    becomes

    CONVERT(DateTime, LEFT([INITIAL_ASSESSMENT_TIME],2) + ':' + RIGHT([INITIAL_ASSESSMENT_TIME],2), 108) AS AssessTime

    Then, on your Concatanate question:

    [ARRIVAL_DATE] [ARRIVAL_TIME]

    ------------------------- ------------------------

    2006-12-31 00:00:00.000 1900-01-01 00:26:00.000

    2006-12-31 00:00:00.000 1900-01-01 00:28:00.000

    Select CONVERT(DateTime, [ARRIVAL_DATE], 120) + CONVERT(DateTime, RIGHT(CONVERT(Char(19), [ARRIVAL_TIME], 120), 12), 114)

    That seems round-about but it will work and is safe. I am assuming your date and time as displayed is machine default but the idea is to take no chances. The day you can specify as a date (it may already be a date field - you didn't say) but I would not take a chance with a straight addition of time. That's why the convert to a var, cut off the bit I want, and then back to a DateTime but using the format specifier.

    Hope this helps.

  • Thanks Robert

    This makes sense to me. 🙂

  • This returns a syntax error:

    Select CONVERT(DateTime, [ARRIVAL_DATE], 120) + CONVERT(DateTime, RIGHT(CONVERT(Char(19), [ARRIVAL_TIME], 120), 12), 114)

    but a small amendment and slight repositioning of the parentheses sorted that:

    SELECT CONVERT(DateTime, [ARRIVAL_DATE], 120) + CONVERT(DateTime, RIGHT(CONVERT(Char(19), [ARRIVAL_TIME], 120), 114)) AS ArrDateTime

  • While this appears to be solved, I was just wondering why use CONVERT or CAST? I would think that if you already have DateTime data types, why not use the date arithmetic functions:

    SELECT DATEADD(s,DATEDIFF(s,[ARRIVAL_TIME],'01/01/1900 00:00:00'),[ARRIVAL_DATE]) AS ARRIVAL_DT

    This could be done with any combination of two DateTime fields where one has the time and the other has the date, and automatically solves any problem of where the time field is > 24 hrs. However, I am making an assumption that a time that would represent a duration of > 24 hrs and 48 hrs but < 72 hrs in a DateTime value of 1/3/1900 hh:mm:ss, etc., etc...

    Am I at least thinking this through correctly?

    Steve

    (aka smunson)

    :):):)

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • smunson (6/10/2008)


    While this appears to be solved, I was just wondering why use CONVERT or CAST? I would think that if you already have DateTime data types, why not use the date arithmetic functions:

    SELECT DATEADD(s,DATEDIFF(s,[ARRIVAL_TIME],'01/01/1900 00:00:00'),[ARRIVAL_DATE]) AS ARRIVAL_DT

    This could be done with any combination of two DateTime fields where one has the time and the other has the date, and automatically solves any problem of where the time field is > 24 hrs. However, I am making an assumption that a time that would represent a duration of > 24 hrs and 48 hrs but < 72 hrs in a DateTime value of 1/3/1900 hh:mm:ss, etc., etc...

    Am I at least thinking this through correctly?

    Steve

    (aka smunson)

    :):):)

    Steve

    First, I would like to point out that I am not responsible for the failings of the data collection system!

    The time data as originally received was not all DateTime: the arrival date and arrival time were two separate DateTime fields (I have no idea why); all the other times were simply 4 digit numbers to represent 24 hour clock times (except where the field was empty).

    Consequently, for each record, where one time is before midnight but another time is after midnight the time interval appears to be negative.

    I think you are suggesting that by concatenating the 24hr clock times with the arrival date the other timings would work in the way you describe above. Unfortunately I don't think that is so.

    Regards

    Tim

  • Please don't think that I'm making you responsible for a data collection faillure - most of us are put into positions of having to make do with less than ideal data. My difficulty was understanding clearly exactly what you had to work with, and my impression was that you had two DateTime fields to work with, as opposed to separate date & time fields.

    Now that I've got that mess straightened out in at least MY head...

    Provided that each 4 digit time field has an associated date field, it would seem reasonably straightforward to be able to use STRING functions to tie the two together into something that will implicitly convert within the date arithmetic functions I referred to previously... or am I still confused?

    Here's the concept, which may or may not match the data you had to work with:

    DECLARE @STARTDATE DateTime, @ENDDATE DateTime

    DECLARE @SD char(10), @ED char(10), @ST char(4), @et char(4)

    SET @SD = '06/01/2008'

    SET @ED = '06/02/2008'

    SET @ST = '1345'

    SET @et = '0215'

    SET @STARTDATE = DATEADD(s, 0, @SD + ' ' + LEFT(@ST,2) + ':' + RIGHT(@ST,2) + ':00')

    SET @ENDDATE = DATEADD(s, 0, @ED + ' ' + LEFT(@ET,2) + ':' + RIGHT(@ET,2) + ':00')

    SET @INTERVAL_TIME = DATEDIFF(s, @STARTDATE, @ENDDATE)

    PRINT @STARTDATE

    PRINT @ENDDATE

    PRINT CAST((@INTERVAL_TIME / 3600.) AS varchar(10)) + ' Hrs'

    Does this at least make sense?

    Steve

    (aka smunson)

    :):):)

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • smunson (6/11/2008)


    Please don't think that I'm making you responsible for a data collection faillure - most of us are put into positions of having to make do with less than ideal data.

    No problem - AFAIK the field type for the various times varies depending on the source of the data, which is clearly something the aggregators need to address. For this reason it is probably best (for me) to work exclusively in DateTime.

    My difficulty was understanding clearly exactly what you had to work with, and my impression was that you had two DateTime fields to work with, as opposed to separate date & time fields.

    Now that I've got that mess straightened out in at least MY head...

    Provided that each 4 digit time field has an associated date field,

    Sadly, this is not the case. Try these (fictitious) data for example:

    [font="Courier New"]

    ArriveDate ArriveTime AssessTime StartTreatTime EndTreatTime LeaveTime

    ----------------------- ----------------------- ---------- -------------- ------------ ---------

    2006/12/31 00:00:00.000 1900/01/01 22:21:00.000 2254 2308 0024 0026

    2006/12/31 00:00:00.000 1900/01/01 22:25:00.000 2226 2228 0154 0154

    2006/12/31 00:00:00.000 1900/01/01 22:37:00.000 2302 2341 2350 2351

    2006/12/31 00:00:00.000 1900/01/01 22:49:00.000 2317 2356 0028 0028

    2006/12/31 00:00:00.000 1900/01/01 23:02:00.000 0001 0016 0024 0025

    2006/12/31 00:00:00.000 1900/01/01 23:16:00.000 2352 0014 0128 0148

    [/font]

    it would seem reasonably straightforward to be able to use STRING functions to tie the two together into something that will implicitly convert within the date arithmetic functions I referred to previously... or am I still confused?

    Here's the concept, which may or may not match the data you had to work with:

    DECLARE @STARTDATE DateTime, @ENDDATE DateTime

    DECLARE @SD char(10), @ED char(10), @ST char(4), @et char(4)

    SET @SD = '06/01/2008'

    SET @ED = '06/02/2008'

    SET @ST = '1345'

    SET @et = '0215'

    SET @STARTDATE = DATEADD(s, 0, @SD + ' ' + LEFT(@ST,2) + ':' + RIGHT(@ST,2) + ':00')

    SET @ENDDATE = DATEADD(s, 0, @ED + ' ' + LEFT(@ET,2) + ':' + RIGHT(@ET,2) + ':00')

    SET @INTERVAL_TIME = DATEDIFF(s, @STARTDATE, @ENDDATE)

    PRINT @STARTDATE

    PRINT @ENDDATE

    PRINT CAST((@INTERVAL_TIME / 3600.) AS varchar(10)) + ' Hrs'

    Does this at least make sense?

    Steve

    (aka smunson)

    :):):)

    I see, yes, it makes sense subject to the limitations above and the difference in date format between the US and UK 🙂

    Thanks

    Tim

    Edited to make data easier to read

  • Now I see the difficulty. There's only one scenario where a solution is impossible - that of the end time being MORE than 24 hours beyond the start time. In that case, you have NO way to determine that such a time period did occur. Assuming that you tell those folks that limitation in advance, and indicate WHY it's entirely impossible, you CAN still just check to see if the end time's character string is less than the start time's character string, and if true, the date for the end time is just adding 1 day to the arrival date. Thanks again for helping me see this more clearly.

    Steve

    (aka smunson)

    :):):)

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • Yeah

    That should never happen.

    But of course it has in the past. 🙁

Viewing 9 posts - 16 through 23 (of 23 total)

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