help converting dates to consistent format

[gcue]

i am working with a list of dates - depending on the date returned sometimes the result is formatted in the formats shown below

M/dd/yyyy

M/d/yyyy

MM/dd/yyyy

MM/d/yyyy

(at least year is consistently yyyy)  

Is there any way to convert any of these types to a consistent format (ie MM/dd/yyyy)

i see scripts on here that convert from a syntax to another.. but the input syntax has to specify what format the input date is in

i was thinking doing a stringsplit of the "/" character and then inserting zeroes where appropriate then piecing the values back together but that doesnt seem as efficient

expandcollapse popup

#include <array.au3>

$msg_normal = 0

$date = "2/3/2012"
;~ $date = "2/03/2012"
;~ $date = "02/3/2012"
;~ $date = "02/03/2012"

$date_formatted = FormatDate($date)

Debug($date_formatted)

Func FormatDate($old_date)

    $array = StringSplit($old_date, "/")

    For $x = 1 To 2
        If StringLen($array[$x]) = 1 Then
            $array[$x] = "0" & $array[$x]
        EndIf
    Next

    Local $new_date

    For $x = 1 To 3
        $new_date &= $array[$x] & "/"
    Next

    $new_date = StringTrimRight($new_date, 1)

    Return $new_date

EndFunc   ;==>FormatDate

Func Debug($variable1 = "", $variable2 = "", $variable3 = "")

;~  #include <array.au3>
;~  $msg_normal = 0

    If IsArray($variable1) Then
        _ArrayDisplay($variable1)
    Else
        If $variable2 <> "" Then
            $variable1 &= @CRLF & $variable2
        EndIf

        If $variable3 <> "" Then
            $variable1 &= @CRLF & $variable3
        EndIf

        ClipPut($variable1)
        MsgBox($msg_normal, "Debug", $variable1)
    EndIf

EndFunc   ;==>Debug

any thoughts?

thanks in advance!

 

Edited May 19, 2015 by gcue

[Danp2]

From what source are you pulling the data? If you don't know the format, how are you going to differentiate between 01/02/2015 and 02/01/2015?

[AdamUL]

Give this a try.  It should do what you need.  

Global $sDate = ""
$sDate = "2/3/2012"
;~ $sDate = "2/03/2012"
;~ $sDate = "02/3/2012"
;~ $sDate = "02/03/2012"

$sDate = StringRegExpReplace($sDate, "^(\d)/(\d)/(\d{4})$", "0$1/0$2/$3")
$sDate = StringRegExpReplace($sDate, "^(\d{2})/(\d)/(\d{4})$", "$1/0$2/$3")
$sDate = StringRegExpReplace($sDate, "^(\d)/(\d{2})/(\d{4})$", "0$1/$2/$3")

ConsoleWrite($sDate & @CRLF)

 

Adam

Edited May 19, 2015 by AdamUL

[Danp2]

Am I imagining things or did the original post not include formats like dd/MM/YYYY?

[gcue]

its always month then day then year.. but the single/double digits vary depending on what month/day it is

[gcue]

Give this a try.  It should do what you need.  

Global $sDate = ""
$sDate = "2/3/2012"
;~ $sDate = "2/03/2012"
;~ $sDate = "02/3/2012"
;~ $sDate = "02/03/2012"

$sDate = StringRegExpReplace($sDate, "^(\d)/(\d)/(\d{4})$", "0$1/0$2/$3")
$sDate = StringRegExpReplace($sDate, "^(\d{2})/(\d)/(\d{4})$", "$1/0$2/$3")
$sDate = StringRegExpReplace($sDate, "^(\d)/(\d{2})/(\d{4})$", "0$1/$2/$3")

ConsoleWrite($sDate & @CRLF)

 

Adam

​works great thanks adam!

[UEZ]

A little bit shorter:

Global $sDate = ""
;~ $sDate = "2/3/2012"
;~ $sDate = "2/03/2012"
$sDate = "02/3/2012"

$aDate = StringRegExp($sDate, "(\d+)\/(\d+)\/(\d+)", 3)
$sDateFormat = StringFormat("%02i/%02i/%04i", $aDate[0], $aDate[1], $aDate[2])

ConsoleWrite($sDateFormat & @CRLF)

 

[gcue]

i am able to pull time now from those dates.  so now the format varies between h and hh.  minutes and AM/PM are consistent # of characters.  the data looks like this now.

$date = "2/3/2012 8:38 PM"
;~ $date = "2/03/2012 08:38 PM"
;~ $date = "02/3/2012 8:38 AM"
;~ $date = "02/03/2012 08:38 AM"

sorry there will be no more variations of the data. the export of dates i am getting didnt have times before - wont change again

thanks again in advance!

 

[UEZ]

Global $date
;~ $date = "2/3/2012 8:38 PM"
;~ $date = "2/03/2012 08:38 PM"
;~ $date = "02/3/2012 8:38 AM"
;~ $date = "02/03/2012 08:38 AM"

$aDate = StringRegExp($date, "(?i)(\d+)\/(\d+)\/(\d+)\h+(\d+):(\d+)\h+(\w+)", 3)
$sDateFormat = StringFormat("%02i/%02i/%04i %02i:%02i %s", $aDate[0], $aDate[1], $aDate[2], $aDate[3], $aDate[4], $aDate[5])

ConsoleWrite($sDateFormat & @CRLF)

 

[gcue]

 

Global $date
;~ $date = "2/3/2012 8:38 PM"
;~ $date = "2/03/2012 08:38 PM"
;~ $date = "02/3/2012 8:38 AM"
;~ $date = "02/03/2012 08:38 AM"

$aDate = StringRegExp($date, "(?i)(\d+)\/(\d+)\/(\d+)\h+(\d+):(\d+)\h+(\w+)", 3)
$sDateFormat = StringFormat("%02i/%02i/%04i %02i:%02i %s", $aDate[0], $aDate[1], $aDate[2], $aDate[3], $aDate[4], $aDate[5])

ConsoleWrite($sDateFormat & @CRLF)

 

 

​beautiful!!! 

thanks UEZ!