Jump to content
Sign in to follow this  
BreCalmor

[Solved] Date text conversion for DateDiff ()

Recommended Posts

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 by BreCalmor

Share this post


Link to post
Share on other sites
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)

 

Spoiler

Using OS: Win 7 Professional, Using AutoIt Ver(s): 3.3.6.1 / 3.3.8.1

AutoIt_Rus_Community.png AutoIt Russian Community

My Work...

Spoiler

AutoIt_Icon_small.pngProjects: ATT - Application Translate Tool {new}| BlockIt - Block files & folders {new}| SIP - Selected Image Preview {new}| SISCABMAN - SciTE Abbreviations Manager {new}| AutoIt Path Switcher | AutoIt Menu for Opera! | YouTube Download Center! | Desktop Icons Restorator | Math Tasks | KeyBoard & Mouse Cleaner | CaptureIt - Capture Images Utility | CheckFileSize Program

AutoIt_Icon_small.pngUDFs: OnAutoItErrorRegister - Handle AutoIt critical errors {new}| AutoIt Syntax Highlight {new}| Opera Library! | Winamp Library | GetFolderToMenu | Custom_InputBox()! | _FileRun UDF | _CheckInput() UDF | _GUIInputSetOnlyNumbers() UDF | _FileGetValidName() UDF | _GUICtrlCreateRadioCBox UDF | _GuiCreateGrid() | _PathSplitByRegExp() | _GUICtrlListView_MoveItems - UDF | GUICtrlSetOnHover_UDF! | _ControlTab UDF! | _MouseSetOnEvent() UDF! | _ProcessListEx - UDF | GUICtrl_SetResizing - UDF! | Mod. for _IniString UDFs | _StringStripChars UDF | _ColorIsDarkShade UDF | _ColorConvertValue UDF | _GUICtrlTab_CoverBackground | CUI_App_UDF | _IncludeScripts UDF | _AutoIt3ExecuteCode | _DragList UDF | Mod. for _ListView_Progress | _ListView_SysLink | _GenerateRandomNumbers | _BlockInputEx | _IsPressedEx | OnAutoItExit Handler | _GUICtrlCreateTFLabel UDF | WinControlSetEvent UDF | Mod. for _DirGetSizeEx UDF
 
AutoIt_Icon_small.pngExamples: 
ScreenSaver Demo - Matrix included | Gui Drag Without pause the script | _WinAttach()! | Turn Off/On Monitor | ComboBox Handler Example | Mod. for "Thinking Box" | Cool "About" Box | TasksBar Imitation Demo

Like the Projects/UDFs/Examples? Please rate the topic (up-right corner of the post header: Rating AutoIt_Rating.gif)

* === My topics === *

==================================================
My_Userbar.gif
==================================================

 

 

 

AutoIt is simple, subtle, elegant. © AutoIt Team

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
Malkey

A complete solution.

#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 by Malkey

Share this post


Link to post
Share on other sites
BreCalmor

A complete solution.

#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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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 by zerobazar

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

post-42149-1226794886_thumb.jpg

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.