[Solved] Date text conversion for DateDiff ()

[BreCalmor]

How do I get this:

$sStartDate = "11/7/2008"

$sStartTime = "11:30AM"

$sEndDate = "11/7/2008"

$sEndTime = "3:00PM"

Into this format:

YYYY/MM/DD[ HH:MM:SS]

So I can do:

_DateDiff ("h", $sStart, $sEnd)

And figure out the there are 3.5 hours between them?

Thanks,

Bre

Edited November 8, 2008 by BreCalmor

[MrCreatoR]

Using _DateTimeConvert function from here you can try like this:

#include <Date.au3>

$sStartDate = "11/7/2008"
$sStartTime = "11:30AM"
$sEndDate = "11/7/2008"
$sEndTime = "3:00PM"

$sStart = _DateTimeConvert($sStartDate & " " & $sStartTime, 0, "MM/D/YYYY HH:MM", "YYYY/MM/DD HH:MM")
$sEnd = _DateTimeConvert($sEndDate & " " & $sEndTime, 0, "MM/D/YYYY H:MM", "YYYY/MM/DD HH:MM")

$sDiff = _DateDiff("h", $sStart, $sEnd)

MsgBox(64, "_DateTimeConvert", $sDiff)

[BreCalmor]

Using _DateTimeConvert function from here you can try like this:

#include <Date.au3>

$sStartDate = "11/7/2008"
$sStartTime = "11:30AM"
$sEndDate = "11/7/2008"
$sEndTime = "3:00PM"

$sStart = _DateTimeConvert($sStartDate & " " & $sStartTime, 0, "MM/D/YYYY HH:MM", "YYYY/MM/DD HH:MM")
$sEnd = _DateTimeConvert($sEndDate & " " & $sEndTime, 0, "MM/D/YYYY H:MM", "YYYY/MM/DD HH:MM")

$sDiff = _DateDiff("h", $sStart, $sEnd)

MsgBox(64, "_DateTimeConvert", $sDiff)

That isn't a complete solution because you have to know whether you have 1 or 2 digits in each of the month, day, or hour fields to set the input format correctly.

Or am I missing something?

[Malkey]

A complete solution.

expandcollapse popup

#include <Date.au3>

$sStartDate = "11/7/2008"
$sStartTime = "11:30AM"
$sEndDate = "11/7/2008"
$sEndTime = "3:00PM"

$sStart = _DateTimeConvert($sStartDate & " " & $sStartTime, 0, "MM/D/YYYY HH:MM", "YYYY/MM/DD HH:MM")
ConsoleWrite("$sStart  = "& $sStart & @CRLF)
$sEnd = _DateTimeConvert($sEndDate & " " & $sEndTime, 1, "MM/D/YYYY H:MM", "YYYY/MM/DD HH:MM")
ConsoleWrite("$sEnd  = "& $sEnd & @CRLF)

$iDateCalcD = _DateDiff('D', $sStart, $sEnd)
$iDateCalch = _DateDiff('h', $sStart, $sEnd)
$iDateCalcm = _DateDiff('n', $sStart, $sEnd)
MsgBox(4096, "", "Time difference  " & $iDateCalcD & " Days " & Int($iDateCalch - $iDateCalcD * 24) & " hr " & _
        Int($iDateCalcm - $iDateCalch * 60) & "min")

Func _DateTimeConvert($sDateTime, $iCorrectAmPm = 0, $sDTFrmt = "DD/MM/YYYY HH:MM", $sRetFrmt = "YYYY/MM/DD HH:MM:SS", $sAddYear = 20)
    Local $sAM_PM = StringStripWS(StringRegExpReplace($sDateTime, "\d+|:|/", ""), 8)

    Local $aDateTime_Parts = StringSplit($sDTFrmt, ":/ ")
    Local $sDay, $sMonth, $sYear, $sHour, $sMinute, $sSecond, $sPart, $iOccur = 1

    For $i = 1 To $aDateTime_Parts[0]
        $sPart = StringMid($sDateTime, StringInStr($sDTFrmt, $aDateTime_Parts[$i], 0, $iOccur), StringLen($aDateTime_Parts[$i]))

        If StringInStr($aDateTime_Parts[$i], "D") Then
            $sDay = $sPart
        ElseIf $sMonth = "" And StringInStr($aDateTime_Parts[$i], "M") Then
            $sMonth = $sPart
            $iOccur = -1
        ElseIf StringInStr($aDateTime_Parts[$i], "Y") Then
            $sYear = $sPart
        ElseIf StringInStr($aDateTime_Parts[$i], "H") Then
            $sHour = $sPart
        ElseIf $sMonth <> "" And StringInStr($aDateTime_Parts[$i], "M") Then
            $sMinute = $sPart
        ElseIf StringInStr($aDateTime_Parts[$i], "S") Then
            $sSecond = $sPart
        EndIf
    Next

    If $iCorrectAmPm And $sAM_PM = "PM" Then
        If $sHour < 12 Then $sHour += 12
        If $sHour = 12 Then $sHour = "00"
    ElseIf $iCorrectAmPm And $sAM_PM = "AM" Then
        If $sHour > 12 Then $sHour -= 12
        If $sHour = "00" Then $sHour = 12
    EndIf

    $aDateTime_Parts = StringSplit($sRetFrmt, ":/ ")

    For $i = 1 To $aDateTime_Parts[0]
        If $aDateTime_Parts[$i] = "AM" Or $aDateTime_Parts[$i] = "PM" Then
            ContinueLoop
        ElseIf StringInStr($aDateTime_Parts[$i], "D") Then
            $sPart = $sDay
        ElseIf Not StringInStr($sRetFrmt, $sMonth) And StringInStr($aDateTime_Parts[$i], "M") Then
            $sPart = $sMonth
        ElseIf StringInStr($aDateTime_Parts[$i], "Y") Then
            $sYear = StringRight($sYear, StringLen($aDateTime_Parts[$i]))
            $sPart = $sYear
            If StringLen($sYear) < 4 And StringLen($aDateTime_Parts[$i]) = 4 Then $sPart = $sAddYear & $sYear
        ElseIf StringInStr($aDateTime_Parts[$i], "H") Then
            $sPart = $sHour
        ElseIf StringInStr($sRetFrmt, $sMonth) And StringInStr($aDateTime_Parts[$i], "M") Then
            $sPart = $sMinute
        ElseIf StringInStr($aDateTime_Parts[$i], "S") Then
            $sPart = $sSecond
        EndIf

        If StringLen($sPart) = 1 Then
            $sPart = "0" & $sPart
            $sPart = StringRight($sPart, StringLen($aDateTime_Parts[$i]))
        EndIf

        $sRetFrmt = StringReplace($sRetFrmt, $aDateTime_Parts[$i], $sPart, 1)
    Next

    Return $sRetFrmt
EndFunc   ;==>_DateTimeConvert

Edited November 8, 2008 by Malkey

[BreCalmor]

A complete solution.

expandcollapse popup

#include <Date.au3>

$sStartDate = "11/7/2008"
$sStartTime = "11:30AM"
$sEndDate = "11/7/2008"
$sEndTime = "3:00PM"

$sStart = _DateTimeConvert($sStartDate & " " & $sStartTime, 0, "MM/D/YYYY HH:MM", "YYYY/MM/DD HH:MM")
ConsoleWrite("$sStart  = "& $sStart & @CRLF)
$sEnd = _DateTimeConvert($sEndDate & " " & $sEndTime, 1, "MM/D/YYYY H:MM", "YYYY/MM/DD HH:MM")
ConsoleWrite("$sEnd  = "& $sEnd & @CRLF)

$iDateCalcD = _DateDiff('D', $sStart, $sEnd)
$iDateCalch = _DateDiff('h', $sStart, $sEnd)
$iDateCalcm = _DateDiff('n', $sStart, $sEnd)
MsgBox(4096, "", "Time difference  " & $iDateCalcD & " Days " & Int($iDateCalch - $iDateCalcD * 24) & " hr " & _
        Int($iDateCalcm - $iDateCalch * 60) & "min")

Func _DateTimeConvert($sDateTime, $iCorrectAmPm = 0, $sDTFrmt = "DD/MM/YYYY HH:MM", $sRetFrmt = "YYYY/MM/DD HH:MM:SS", $sAddYear = 20)
    Local $sAM_PM = StringStripWS(StringRegExpReplace($sDateTime, "\d+|:|/", ""), 8)

    Local $aDateTime_Parts = StringSplit($sDTFrmt, ":/ ")
    Local $sDay, $sMonth, $sYear, $sHour, $sMinute, $sSecond, $sPart, $iOccur = 1

    For $i = 1 To $aDateTime_Parts[0]
        $sPart = StringMid($sDateTime, StringInStr($sDTFrmt, $aDateTime_Parts[$i], 0, $iOccur), StringLen($aDateTime_Parts[$i]))

        If StringInStr($aDateTime_Parts[$i], "D") Then
            $sDay = $sPart
        ElseIf $sMonth = "" And StringInStr($aDateTime_Parts[$i], "M") Then
            $sMonth = $sPart
            $iOccur = -1
        ElseIf StringInStr($aDateTime_Parts[$i], "Y") Then
            $sYear = $sPart
        ElseIf StringInStr($aDateTime_Parts[$i], "H") Then
            $sHour = $sPart
        ElseIf $sMonth <> "" And StringInStr($aDateTime_Parts[$i], "M") Then
            $sMinute = $sPart
        ElseIf StringInStr($aDateTime_Parts[$i], "S") Then
            $sSecond = $sPart
        EndIf
    Next

    If $iCorrectAmPm And $sAM_PM = "PM" Then
        If $sHour < 12 Then $sHour += 12
        If $sHour = 12 Then $sHour = "00"
    ElseIf $iCorrectAmPm And $sAM_PM = "AM" Then
        If $sHour > 12 Then $sHour -= 12
        If $sHour = "00" Then $sHour = 12
    EndIf

    $aDateTime_Parts = StringSplit($sRetFrmt, ":/ ")

    For $i = 1 To $aDateTime_Parts[0]
        If $aDateTime_Parts[$i] = "AM" Or $aDateTime_Parts[$i] = "PM" Then
            ContinueLoop
        ElseIf StringInStr($aDateTime_Parts[$i], "D") Then
            $sPart = $sDay
        ElseIf Not StringInStr($sRetFrmt, $sMonth) And StringInStr($aDateTime_Parts[$i], "M") Then
            $sPart = $sMonth
        ElseIf StringInStr($aDateTime_Parts[$i], "Y") Then
            $sYear = StringRight($sYear, StringLen($aDateTime_Parts[$i]))
            $sPart = $sYear
            If StringLen($sYear) < 4 And StringLen($aDateTime_Parts[$i]) = 4 Then $sPart = $sAddYear & $sYear
        ElseIf StringInStr($aDateTime_Parts[$i], "H") Then
            $sPart = $sHour
        ElseIf StringInStr($sRetFrmt, $sMonth) And StringInStr($aDateTime_Parts[$i], "M") Then
            $sPart = $sMinute
        ElseIf StringInStr($aDateTime_Parts[$i], "S") Then
            $sPart = $sSecond
        EndIf

        If StringLen($sPart) = 1 Then
            $sPart = "0" & $sPart
            $sPart = StringRight($sPart, StringLen($aDateTime_Parts[$i]))
        EndIf

        $sRetFrmt = StringReplace($sRetFrmt, $aDateTime_Parts[$i], $sPart, 1)
    Next

    Return $sRetFrmt
EndFunc   ;==>_DateTimeConvert

Not quite, for the very same reason.

If you change these values:

$sStartDate = "6/7/2008"

$sEndDate = "6/7/2008"

It won't work, unless you MANUALLY change:

$sStart = _DateTimeConvert($sStartDate & " " & $sStartTime, 0, "MM/D/YYYY HH:MM", "YYYY/MM/DD HH:MM")

$sEnd = _DateTimeConvert($sEndDate & " " & $sEndTime, 1, "MM/D/YYYY H:MM", "YYYY/MM/DD HH:MM")

To:

$sStart = _DateTimeConvert($sStartDate & " " & $sStartTime, 0, "M/D/YYYY HH:MM", "YYYY/MM/DD HH:MM")

$sEnd = _DateTimeConvert($sEndDate & " " & $sEndTime, 1, "M/D/YYYY H:MM", "YYYY/MM/DD HH:MM")

The jury is still out on the AM/PM thing.

I am not sure if I am doing something wrong, or if there is no way in AutoIt to manipulate date/time strings. It isn't practical for me to change the code for the _DateTimeConvert passed values from MM/DD/YYYY on 12/31/2008 to M/D/YYYY on 1/1/2008 (and other similiar changes) twice a month just to keep it up.

I am tired right now, but I will take a look at this function. Maybe there is a way to manipulate the function so that it gets the month, day, and hour whether it is 1 or 2 digits and returns the value based on the appropriate MM, DD, or HH.

Thanks for sending me in the right direction.

Bre

[zerobazar]

This should work:

#include <Date.au3>

Local $sStartDate = "11/7/2008"
Local $sStartTime = "11:30AM"
Local $sEndDate = "11/7/2008"
Local $sEndTime = "3:00PM"

$sStart = _DateTimeConvert($sStartDate, $sStartTime)
$sEnd = _DateTimeConvert($sEndDate, $sEndTime)
MsgBox(0,"_DateDiff", $sStart & @LF & $sEnd & @LF & "Diff: " & _DateDiff ("h", $sStart, $sEnd))

Func _DateTimeConvert($Date, $Time)
    Local $temp, $res
    $temp = StringRegExpReplace($Date, "(\d\d?)/(\d\d?)/(\d{4})","\3/\1/\2") 
    $temp = StringSplit($temp, "/")
    $res = $temp[1] & "/" & StringFormat("%02i", $temp[2]) & "/" & StringFormat("%02i", $temp[3])
    
    $temp = StringRegExp($Time, "(\d\d?):(\d\d?)(AM|PM)",1) 
    If $temp[2] = "PM" Then $temp[0] = $temp[0] + 12
    $res = $res & " " & StringFormat("%02i", $temp[0]) & ":" & StringFormat("%02i", $temp[1]) & ":00"
    Return $res
EndFunc

Best Regards.

[Malkey]

Func _DateTimeConvert($Date, $Time)
    Local $temp, $res
    $temp = StringRegExpReplace($Date, "(\d\d?)/(\d\d?)/(\d{4})","\3/\1/\2") 
    $temp = StringSplit($temp, "/")
    $res = $temp[1] & "/" & StringFormat("%02i", $temp[2]) & "/" & StringFormat("%02i", $temp[3])
    
    $temp = StringRegExp($Time, "(\d\d?):(\d\d?)(AM|PM)",1) 
    If $temp[2] = "PM" Then $temp[0] = $temp[0] + 12
    $res = $res & " " & StringFormat("%02i", $temp[0]) & ":" & StringFormat("%02i", $temp[1]) & ":00"
    Return $res
EndFuncoÝ÷ Ûú®¢×¢c¡º'¶j×g¢{Þ®×îËb¢|Þ®ÚͪÈË^iÚ¶¬~æjÖ®¶­sbb33c·&W2Òb33c·&W2fײgV÷C²gV÷C²fײ7G&ætf÷&ÖBgV÷C²S&gV÷C²Âb33c·FV׳ÒfײgV÷C³¢gV÷C²fײ7G&ætf÷&ÖBgV÷C²S&gV÷C²Âb33c·FV׳ÒfײgV÷C³£gV÷C°

It does not make any difference to the result which way is used.

[zerobazar]

This is beautiful Malkey!

You make me discover how to use StringFormat with more than 1 variable...

So we now have:

#include <Date.au3>

Local $sStartDate = "11/7/2008" 
Local $sStartTime = "11:30AM"
Local $sEndDate = "11/7/2008"
Local $sEndTime = "3:00PM"

$sStart = _DateTimeConvert($sStartDate, $sStartTime)
$sEnd = _DateTimeConvert($sEndDate, $sEndTime)
MsgBox(0,"_DateDiff", $sStart & @LF & $sEnd & @LF & "Diff: " & _DateDiff ("h", $sStart, $sEnd))

Func _DateTimeConvert($Date, $Time)
    Local $temp, $res
    $temp = StringRegExpReplace($Date, "(\d\d?)/(\d\d?)/(\d{4})","\3/\1/\2") 
    $temp = StringSplit($temp, "/")
    $res = StringFormat("%4i/%02i/%02i", $temp[1], $temp[2], $temp[3])
    
    $temp = StringRegExp($Time, "(\d\d?):(\d\d?)(AM|PM)",1) 
    If $temp[2] = "PM" Then $temp[0] = $temp[0] + 12
    $res &= StringFormat(" %02i:%02i:00", $temp[0], $temp[1])
    Return $res
EndFunc

Many thanks for the hint.

[BreCalmor]

Wow, that was awesome. I threw that in to test it against the website and you know what... they took out the space between the time and the AM/PM.

So, how does your function change with '11:25 AM' vice '11:25AM'. I am using StringStripWS ($sStartTime, 8) for now, and I can leave that if it is best.

Thanks a million for this gem. I have no idea what it is doing, but it works flawlessly.

Bre

[zerobazar]

If you may encounter "11:25 AM" and "11:25AM" at the same time, then a small change will do:

#include <Date.au3>

Local $sStartDate = "11/7/2008" 
Local $sStartTime = "11:30AM"
Local $sEndDate = "11/7/2008"
Local $sEndTime = "3:00PM"

$sStart = _DateTimeConvert($sStartDate, $sStartTime)
$sEnd = _DateTimeConvert($sEndDate, $sEndTime)
MsgBox(0,"_DateDiff", $sStart & @LF & $sEnd & @LF & "Diff: " & _DateDiff ("h", $sStart, $sEnd))

Func _DateTimeConvert($Date, $Time)
    Local $temp, $res
    $temp = StringRegExp($Date, "(\d\d?)/(\d\d?)/(\d{4})",1) 
    $res = StringFormat("%4i/%02i/%02i", $temp[2], $temp[0], $temp[1])
    
    $temp = StringRegExp($Time, "(\d\d?):(\d\d?)[ ]?(AM|PM)",1) 
    If $temp[2] = "PM" Then $temp[0] = $temp[0] + 12
    $res &= StringFormat(" %02i:%02i:00", $temp[0], $temp[1])
    Return $res
EndFunc

It now works on both cases.

Enjoy!

Edit: small improvement...

Edited November 8, 2008 by zerobazar

[zerobazar]

Very well spotted Hubertus ! (I am not an expert of AM/PM, being French...)

Is this better?

#include <Date.au3>

Local $sStartDate = "11/7/2008" 
Local $sStartTime = "12:30AM"
Local $sEndDate = "11/7/2008"
Local $sEndTime = "12:30PM"

$sStart = _DateTimeConvert($sStartDate, $sStartTime)
$sEnd = _DateTimeConvert($sEndDate, $sEndTime)
MsgBox(0,"_DateDiff", $sStart & @LF & $sEnd & @LF & "Diff: " & _DateDiff ("h", $sStart, $sEnd))

Func _DateTimeConvert($Date, $Time)
    Local $temp, $res
    $temp = StringRegExp($Date, "(\d\d?)/(\d\d?)/(\d{4})",1) 
    $res = StringFormat("%4i/%02i/%02i", $temp[2], $temp[0], $temp[1])
    
    $temp = StringRegExp($Time, "(\d\d?):(\d\d?)[ ]?(AM|PM)",1) 
    If $temp[0] = 12 Then $temp[0] = 0
    If $temp[2] = "PM" Then $temp[0] = $temp[0] + 12
    $res &= StringFormat(" %02i:%02i:00", $temp[0], $temp[1])
    Return $res
EndFunc

Cheers.

[BreCalmor]

That was great guys !!!

Thanks !!

[BreCalmor]

Now I am getting an error (please see attachment)

On the 2nd and 3rd line of this function. The attachment is for the one on the 3rd line, but I got one on the second line earlier.

Func _DateTimeConvert($Date, $Time)
    Local $temp, $res
    $temp = StringRegExp($Date, "(\d\d?)/(\d\d?)/(\d{4})",1) 
    $res = StringFormat("%4i/%02i/%02i", $temp[2], $temp[0], $temp[1])
    
    $temp = StringRegExp($Time, "(\d\d?):(\d\d?)[ ]?(AM|PM)",1) 
    If $temp[0] = 12 Then $temp[0] = 0
    If $temp[2] = "PM" Then $temp[0] = $temp[0] + 12
    $res &= StringFormat(" %02i:%02i:00", $temp[0], $temp[1])
    Return $res
EndFunc

I think it has something to do with the time being "10:00AM" vice "10:00 AM".

I don't know enough about expressions to figure this out. Please help.

Thanks,

Bre

[BreCalmor]

Ok, so I added a string routine to add a space before the AM/PM if it wasn't there.

Errors seem to be gone for now.