Android Question [Solved] Parse date from DB

[aeric]

Is there a good way to parse an unknown format datetime string? The values sometime "dd/MM/yyyy", "dd-MM-yyyy", "yyyy-MM-dd", "dd/MM/yyyy HH:mm", "dd-MM-yyyy HH:mm", "yyyy-MM-dd HH:mm"

 

[Erel]

For Each Pattern In Patterns
 Try
  DateTime.DateFormat = Pattern
  Dim d As Long = DateTime.DateParse(...)
  Return d
 Catch
 '
End Try
Next
Log("Failed to parse")
Return 0

 

[aeric]

If I try to use Regex, is this correct?

Log(ParseAnyDate("23/05/2023"))
Log(ParseAnyDate("23/05/2023 17:32"))

Sub ParseAnyDate (strDateTime As String) As Long
    Dim CurrentFormat As String = DateTime.DateFormat
    Dim Format As String = DetermineDateFormat(strDateTime)
    Log(Format)
    DateTime.DateFormat = Format
    Dim LongDate As Long = DateTime.DateParse(strDateTime)
    DateTime.DateFormat = CurrentFormat
    Return LongDate
End Sub

Sub DATE_FORMAT_REGEXPS As Map
    Dim Map1 As Map
    Map1.Initialize
    Map1.Put("(\d\d\d\d\d\d\d\d)", "yyyyMMdd")
    Map1.Put("(\d\d\d\d\d\d\d\d\d\d\d\d)", "yyyyMMddHHmm")
    Map1.Put("(\d\d\d\d\d\d\d\d\d\d\d\d\d\d)", "yyyyMMddHHmmss")
    Map1.Put("(\d\d\d\d\d\d\d\d)\s(\d\d\d\d)", "yyyyMMdd HHmm")
    Map1.Put("(\d\d\d\d\d\d\d\d)\s(\d\d\d\d\d\d)", "yyyyMMdd HHmmss")
    Map1.Put("(\d\d)/(\d\d)/(\d\d\d\d)", "dd/MM/yyyy")
    Map1.Put("(\d\d)-(\d\d)-(\d\d\d\d)", "dd-MM-yyyy")
    Map1.Put("(\d\d\d\d)/(\d\d)/(\d\d)", "yyyy/MM/dd")
    Map1.Put("(\d\d\d\d)-(\d\d)-(\d\d)", "yyyy-MM-dd")
    Map1.Put("(\d\d)\s[a-zA-Z]{3}\s(\d\d\d\d)", "dd MMM yyyy")
    Map1.Put("(\d\d)\s(January|February|March|April|May|June|July|August|September|October|November|December)\s(\d\d\d\d)", "dd MMMM yyyy")
    Map1.Put("(\d\d)/(\d\d)/(\d\d\d\d)\s(\d\d):(\d\d)", "dd/MM/yyyy HH:mm")
    Map1.Put("(\d\d)-(\d\d)-(\d\d\d\d)\s(\d\d):(\d\d)", "dd-MM-yyyy HH:mm")
    Map1.Put("(\d\d\d\d)/(\d\d)/(\d\d)\s(\d\d):(\d\d)", "yyyy/MM/dd HH:mm")
    Map1.Put("(\d\d\d\d)-(\d\d)-(\d\d)\s(\d\d):(\d\d)", "yyyy-MM-dd HH:mm")
    Map1.Put("(\d\d)\s[a-zA-Z]{3}\s(\d\d\d\d)\s(\d\d):(\d\d)", "dd MMM yyyy HH:mm")
    Map1.Put("(\d\d)\s(January|February|March|April|May|June|July|August|September|October|November|December)\s(\d\d\d\d)\s(\d\d):(\d\d)", "dd MMMM yyyy HH:mm")
    Map1.Put("(\d\d)/(\d\d)/(\d\d\d\d)\s(\d\d):(\d\d):(\d\d)", "dd/MM/yyyy HH:mm:ss")
    Map1.Put("(\d\d)-(\d\d)-(\d\d\d\d)\s(\d\d):(\d\d):(\d\d)", "dd-MM-yyyy HH:mm:ss")
    Map1.Put("(\d\d\d\d)/(\d\d)/(\d\d)\s(\d\d):(\d\d):(\d\d)", "yyyy/MM/dd HH:mm:ss")
    Map1.Put("(\d\d\d\d)-(\d\d)-(\d\d)\s(\d\d):(\d\d):(\d\d)", "yyyy-MM-dd HH:mm:ss")
    Map1.Put("(\d\d)\s[a-zA-Z]{3}\s(\d\d\d\d)\s(\d\d):(\d\d):(\d\d)", "dd MMM yyyy HH:mm:ss")
    Map1.Put("(\d\d)\s(January|February|March|April|May|June|July|August|September|October|November|December)\s(\d\d\d\d)\s(\d\d):(\d\d):(\d\d)", "dd MMMM yyyy HH:mm:ss")
    Return Map1
End Sub

Sub DetermineDateFormat (dateString As String) As String
    For Each pattern In DATE_FORMAT_REGEXPS.Keys
        If Regex.IsMatch(pattern, dateString) Then Return DATE_FORMAT_REGEXPS.Get(pattern)
    Next
    Return Null
End Sub

Edited

 

[aeric]

I need to use [a-zA-Z] instead of [a-z]

 

[aeric]

Ouch, full month name can be longer than 4

 

[aeric]

Sub DATE_FORMAT_REGEXPS As Map
    Dim Map1 As Map
    Map1.Initialize
    Map1.Put("(\d\d\d\d\d\d\d\d)", "yyyyMMdd")
    Map1.Put("(\d\d\d\d\d\d\d\d\d\d\d\d)", "yyyyMMddHHmm")
    Map1.Put("(\d\d\d\d\d\d\d\d\d\d\d\d\d\d)", "yyyyMMddHHmmss")
    Map1.Put("(\d\d\d\d\d\d\d\d)\s(\d\d\d\d)", "yyyyMMdd HHmm")
    Map1.Put("(\d\d\d\d\d\d\d\d)\s(\d\d\d\d\d\d)", "yyyyMMdd HHmmss")
    Map1.Put("(\d\d)/(\d\d)/(\d\d\d\d)", "dd/MM/yyyy")
    Map1.Put("(\d\d)-(\d\d)-(\d\d\d\d)", "dd-MM-yyyy")
    Map1.Put("(\d\d\d\d)/(\d\d)/(\d\d)", "yyyy/MM/dd")
    Map1.Put("(\d\d\d\d)-(\d\d)-(\d\d)", "yyyy-MM-dd")
    Map1.Put("(\d\d)\s[a-zA-Z]{3}\s(\d\d\d\d)", "dd MMM yyyy")
    Map1.Put("(\d\d)\s(January|February|March|April|May|June|July|August|September|October|November|December)\s(\d\d\d\d)", "dd MMMM yyyy")
    Map1.Put("(\d\d)/(\d\d)/(\d\d\d\d)\s(\d\d):(\d\d)", "dd/MM/yyyy HH:mm")
    Map1.Put("(\d\d)-(\d\d)-(\d\d\d\d)\s(\d\d):(\d\d)", "dd-MM-yyyy HH:mm")
    Map1.Put("(\d\d\d\d)/(\d\d)/(\d\d)\s(\d\d):(\d\d)", "yyyy/MM/dd HH:mm")
    Map1.Put("(\d\d\d\d)-(\d\d)-(\d\d)\s(\d\d):(\d\d)", "yyyy-MM-dd HH:mm")
    Map1.Put("(\d\d)\s[a-zA-Z]{3}\s(\d\d\d\d)\s(\d\d):(\d\d)", "dd MMM yyyy HH:mm")
    Map1.Put("(\d\d)\s(January|February|March|April|May|June|July|August|September|October|November|December)\s(\d\d\d\d)\s(\d\d):(\d\d)", "dd MMMM yyyy HH:mm")
    Map1.Put("(\d\d)/(\d\d)/(\d\d\d\d)\s(\d\d):(\d\d):(\d\d)", "dd/MM/yyyy HH:mm:ss")
    Map1.Put("(\d\d)-(\d\d)-(\d\d\d\d)\s(\d\d):(\d\d):(\d\d)", "dd-MM-yyyy HH:mm:ss")
    Map1.Put("(\d\d\d\d)/(\d\d)/(\d\d)\s(\d\d):(\d\d):(\d\d)", "yyyy/MM/dd HH:mm:ss")
    Map1.Put("(\d\d\d\d)-(\d\d)-(\d\d)\s(\d\d):(\d\d):(\d\d)", "yyyy-MM-dd HH:mm:ss")
    Map1.Put("(\d\d)\s[a-zA-Z]{3}\s(\d\d\d\d)\s(\d\d):(\d\d):(\d\d)", "dd MMM yyyy HH:mm:ss")
    Map1.Put("(\d\d)\s(January|February|March|April|May|June|July|August|September|October|November|December)\s(\d\d\d\d)\s(\d\d):(\d\d):(\d\d)", "dd MMMM yyyy HH:mm:ss")
    Return Map1
End Sub

 

[aeric]

    Map1.Put("(\d\d)\s[a-zA-Z]{3}\s(\d\d\d\d)", "dd MMM yyyy")
    Map1.Put("(\d\d)\s(January|February|March|April|June|July|August|September|October|November|December)\s(\d\d\d\d)", "dd MMMM yyyy")

I think May has been matched in MMM so no need for MMMM.

 

[Spavlyuk]

It seems you already know about the {n} quantifier, why not use it for all those \d repetitions? You can also use IsMatch2 with the CASE_INSENSITIVE option to match months with different case than the one on your regex. Finally, if you have users with non English locale, parsing English months will throw an exception. Maybe it's better to specify \w instead of the name of the month and parse inside a Try Catch statement.

 

[aeric]

It seems you already know about the {n} quantifier, why not use it for all those \d repetitions? You can also use IsMatch2 with the CASE_INSENSITIVE option to match months with different case than the one on your regex. Finally, if you have users with non English locale, parsing English months will throw an exception. Maybe it's better to specify \w instead of the name of the month and parse inside a Try Catch statement.

I am not good in RegEx. As long as it works most of the time then I can use it.

 

[Spavlyuk]

Simple regex is not very difficult, what I was suggesting was to use \d{8} instead of \d\d\d\d\d\d\d\d, which makes it easier to read and modify if needed.