CODEWITHSUNDEEP
sqlms-access
user2113430
user2113430

Reputation: 1

Converting pseudo 24 Hour Clock time to 12 hour Clock time

I have an MS Access query that shows order turn-around time. Two issues:

  1. The times are in 24 hour clock time time (e.g.: 0105) so when you subtract the validation time from the CPOE time, you get a value that is neither in minutes or hours, so you can’t simply convert it to minutes (i.e.: 0701 – 0527 = 174, the real difference is 94 minutes),
  2. If the CPOE time is prior to midnight and the validation time is after midnight, then the difference is very distorted (i.e.: 2351 – 0024 = 2327, the real difference is 33 minutes).

Anyone know how to resolve this? Here is the SQL query:

 SELECT 
    BI.PTNAME, 
    PHM_POE_HIST.FUNCTION, 
    PHM_ORDERS.VALIDATED_DATE, 
    PHM_ORDERS.VALIDATED_BY, 
    SAT.USER_NAME, 
    PHM_ORDERS.VALIDATED_TIME, 
    Right([TRAN_DATE_TIME],4) AS CPOE_TIME, 
    Abs([validated_time]-[CPOE_TIME]) AS Diff, 
    PHM_POE_HIST.TRAN_DATE_TIME, 
    PHM_POE_HIST.EFF_DATE_TIME, 
    PHM_POE_HIST.HIST_SEQ, BI.NRS_STATION, 
    SITE.NAME, PHM_ORDERS.PMP, 
    PHM_ORDERS.PRIORITY, 
    Trim([GENERIC_NAME] & " " & [STRENGTH] & " " & [STRENGTH_UNIT] & " " & [DOSAGE_FORM] & " " & [LATIN_DIR_ABBR]) AS DESCRIPTION 
FROM 
    ((BI LEFT JOIN 
            (PHM_ORDERS LEFT JOIN PHM_POE_HIST ON (PHM_ORDERS.POE_ORDER_NUMBER = PHM_POE_HIST.POE_ORDER_NUMBER) AND (PHM_ORDERS.ITN = PHM_POE_HIST.ITN))
        ON BI.ITN = PHM_ORDERS.ITN) 
    LEFT JOIN SAT
         ON PHM_ORDERS.VALIDATED_BY = SAT.USER_INITIALS)
    LEFT JOIN SITE 
        ON BI.SITE = SITE.SITE
WHERE 
    (((BI.PTNAME) Is Not Null) 
    AND ((PHM_POE_HIST.FUNCTION)="NW") 
    AND ((PHM_ORDERS.VALIDATED_DATE)=datereltoday(-1)) 
    AND ((PHM_ORDERS.PRIORITY)="STAT"));

Upvotes: 0

Views: 401

Answers (1)

Mitch
Mitch

Reputation: 22311

Just don't do math on dates or times... ever. Military time is a visual format, so parse out the hours and minutes:

Assuming the datatype is char(4)

LEFT(MilitaryTimeColumn, 2) => hours
RIGHT(MilitaryTimeColumn, 2) => minutes

then convert to numbers

CInt(LEFT(MilitaryTimeColumn, 2)) --> hours as integers
CInt(RIGHT(MilitaryTimeColumn, 2)) --> minutes as integers

calculate total minutes

CInt(LEFT(MilitaryTimeColumn, 2)) * 60 + CInt(RIGHT(MilitaryTimeColumn, 2))

then add to the original date

DateAdd("n", 
    CInt(LEFT(MilitaryTimeColumn, 2)) * 60 + CInt(RIGHT(MilitaryTimeColumn, 2)), 
    DateColumn) --> Complete datetime

then use DateDiff to get the distance between to dates.

Upvotes: 4

Related Questions