Android Question Problem DateTime.DateParse if date contains Sep

RB Smissaert · Dec 31, 2023

I am importing .csv files into SQLite and need to convert dates in the format dd-MMM-yyyy into Excel dates.
I have a simple Sub for this:

B4X:

    Public lTicksOff As Long                             'this is set in various Subs, eg InitApp: lTicksOff = DateUtils.SetDate(1970, 1, 1)
    Public ExcelJulianDiff As Long = 25569      'days between Jan 01 1900 and Jan 01 1970
    Public DaySecs As Long = 86400               'number of seconds in a day
    Public DayMiliSecs As Long = 86400000   'number of milli-seconds in a day

'need to make sure to have: DateTime.DateFormat = "dd-MMM-yyyy"
Public Sub EmisDate2XLDate(strEmisDate As String) As Int
    
    Dim lTicks As Long

    'for example:  "15-Jun-2021"
    
    If strEmisDate.Length <> 11 Then
        Return(-1)
    End If
    
    'why this fails with Sep?? eg: 27-Sep-1933
    '-----------------------------------------
    Try
        lTicks = DateTime.DateParse(strEmisDate)
    Catch
        Return(-1)
    End Try
    
    Return ExcelJulianDiff + ((lTicks - lTicksOff) / DayMiliSecs)
    
End Sub

All works fine, except when the month is September, eg: 27-Sep-1933
In that case there would be an error: Unparsable date, caught by the Try Catch.
Any date not containing Sep is fine.

Any idea why this happens and how I can avoid it.
I can think of several work-arounds, but I would like to understand this first.

RBS

emexes · Dec 31, 2023

RB Smissaert said:
Any idea why this happens and how I can avoid it.

Not yet, but I have an idea to help tease out the "why" bit:

Add these two Log lines:

Try
    lTicks = DateTime.DateParse(strEmisDate)

Catch
    Log(strEmisDate)
    Log(DateTime.DateFormat)
    Return(-1)

End Try

RB Smissaert · Dec 31, 2023

emexes said:
Not yet, but I have an idea to help tease out the "why" bit:

Add these two Log lines:

Try lTicks = DateTime.DateParse(strEmisDate) Catch Log(strEmisDate) Log(DateTime.DateFormat) Return(-1) End Try

Had done that already and it doesn't help to find the problem:

eg:

27-Sep-1933
dd-MMM-yyyy
02-Sep-1957
dd-MMM-yyyy
29-Sep-1957
dd-MMM-yyyy
27-Sep-1958
dd-MMM-yyyy
14-Sep-1955
dd-MMM-yyyy
04-Sep-1964
dd-MMM-yyyy

I added a simple workaround, but doesn't look very nice:

B4X:

'need to make sure to have: DateTime.DateFormat = "dd-MMM-yyyy"
Public Sub EmisDate2XLDate(strEmisDate As String) As Int
    
    Dim lTicks As Long
    Dim bSep As Boolean


    'for example:  "15-Jun-2021"
    
    If strEmisDate.Length <> 11 Then
        Return(-1)
    End If
    
    'to avoid getting unparsable date error!
    '---------------------------------------
    If strEmisDate.Contains("Sep") Then
        strEmisDate = strEmisDate.Replace("Sep", "Oct")
        bSep = True
    End If
    
    'why this fails with Sep?? eg: 27-Sep-1933
    '-----------------------------------------
    Try
        lTicks = DateTime.DateParse(strEmisDate)
    Catch
        Log(strEmisDate)
        Log(DateTime.DateFormat)
        Return(-1)
    End Try
    
    If bSep Then 
        lTicks = lTicks - 30 * DayMilliSecs
    End If
    
    Return ExcelJulianDiff + ((lTicks - lTicksOff) / DayMilliSecs)
    
End Sub

RBS

emexes · Dec 31, 2023

RB Smissaert said:
doesn't look very nice:

That's a late but solid entry for Understatement of the Year

Add this Log line too:

Try
    lTicks = DateTime.DateParse(strEmisDate)

Catch
    Log(strEmisDate)
    Log(DateTime.DateFormat)
    Log(DateTime.Date(-1144404000000))    '27-Sep-1933
    Return(-1)

End Try

aeric · Dec 31, 2023

Can we parse ticks for date before 1 Jan 1970 12:00 am?

RB Smissaert · Dec 31, 2023

emexes said:
That's a late but solid entry for Understatement of the Year

Add this Log line too:

Try lTicks = DateTime.DateParse(strEmisDate) Catch Log(strEmisDate) Log(DateTime.DateFormat) Log(DateTime.Date(-1144404000000)) '27-Sep-1933 Return(-1) End Try

View attachment 149126

Agree that work-around looks bad, but it does work.

What is the idea of this:
Log(DateTime.Date(-1144404000000)) '27-Sep-1933 ?

RBS

RB Smissaert · Dec 31, 2023

aeric said:
Can we parse ticks for date before 1 Jan 1970 12:00 am?

Yes, that works fine as far as I can see.

RBS

klaus · Dec 31, 2023

The month abreviation depends on the language setting of the device.
English(United States) > Sep
English(Uniret Kingdom) > Sept
So, I suspect that on your device the language is English(United Kingdom) and it expects 'Sept' for September.
And then it fails in this line: If strEmisDate.Length <> 11 Then

You can test it that way:

B4X:

    DateTime.DateFormat = "MM/dd/yyyy"
    Private mDateTicks As Long
    mDateTicks = DateTime.DateParse("09/21/2021")
    DateTime.DateFormat = "dd-MMM-yyyy"
    Private mDate As String
    mDate = DateTime.Date(mDateTicks)
    Log(mDate)

emexes · Dec 31, 2023

RB Smissaert said:
What is the idea of this:
Log(DateTime.Date(-1144404000000)) '27-Sep-1933 ?

To see what DateTime is expecting to see for September.

But @klaus has stolen my thunder and your epiphany.

RB Smissaert · Dec 31, 2023

klaus said:
The month abreviation depends on the language setting of the device.
English(United States) > Sep
English(Uniret Kingdom) > Sept
So, I suspect that on your device the language is English(United Kingdom) and it expects 'Sept' for September.
And then it fails in this line: If strEmisDate.Length <> 11 Then

You can test it that way:

B4X:

DateTime.DateFormat = "MM/dd/yyyy" Private mDateTicks As Long mDateTicks = DateTime.DateParse("09/21/2021") DateTime.DateFormat = "dd-MMM-yyyy" Private mDate As String mDate = DateTime.Date(mDateTicks) Log(mDate)

Thanks for that.
I did have a look at that but when I saw English (United Kingdom) I thought it would be OK.
Never thought UK would have a different length of shortened month for September.
Should be able to fix this now in a better way.

RBS

emexes · Dec 31, 2023

https://stackoverflow.com/questions...p-no-longer-parses-in-java-17-in-en-gb-locale

klaus · Dec 31, 2023

My device language is normally set to French and, of course, it did not work for any month.
Fortunately I reset it first to English (United Kingdom) and seeing that it is 'Sept', which astonished me.
And then I checked with English(United States) and saw the difference.

ilan · Dec 31, 2023

you need to be careful with parsing dates that contains characters.
if someone will use a different language like hebrew, arabic, russian, .. you may get many crashes.
it is better to store the date as long in your sqlite db and parse it from a long value to what ever you want!

RB Smissaert · Dec 31, 2023

klaus said:
My device language is normally set to French and, of course, it did not work for any month.
Fortunately I reset it first to English (United Kingdom) and seeing that it is 'Sept', which astonished me.
And then I checked with English(United States) and saw the difference.

>> seeing that it is 'Sept', which astonished me

Indeed, I too never thought that all the months would be 3 characters and one would be 4.

RBS

RB Smissaert · Dec 31, 2023

ilan said:
you need to be careful with parsing dates that contains characters.
if someone will use a different language like hebrew, arabic, russian, .. you may get many crashes.
it is better to store the date as long in your sqlite db and parse it from a long value to what ever you want!

I am parsing text files and have no control over these files.
Dates are stored in the SQLite DB as Integer.

RBS

emexes · Dec 31, 2023

RB Smissaert said:
need to convert dates in the format dd-MMM-yyyy into Excel dates

Speaking of late entries... DateUtils.PeriodBetweenInDays might be simpler (obviates need to divide by DateTime.TicksPerDay)

B4X:

DateTime.DateFormat = "dd/MM/yyyy"
Dim ExcelZeroDate As Long = DateTime.DateParse("30/12/1899")    'because 123 thought 1900 was leap year?
Dim DateToConvert As Long = DateTime.DateParse("27/09/1933")
DateTime.DateFormat = DateTime.DeviceDefaultDateFormat

Dim ExcelDate As Int = DateUtils.PeriodBetweenInDays(ExcelZeroDate, DateToConvert).Days

RB Smissaert · Dec 31, 2023

emexes said:
Speaking of late entries... DateUtils.PeriodBetweenInDays might be simpler (obviates need to divide by DateTime.TicksPerDay)

B4X:

DateTime.DateFormat = "dd/MM/yyyy" Dim ExcelZeroDate As Long = DateTime.DateParse("30/12/1899") 'because 123 thought 1900 was leap year? Dim DateToConvert As Long = DateTime.DateParse("27/09/1933") DateTime.DateFormat = DateTime.DeviceDefaultDateFormat Dim ExcelDate As Int = DateUtils.PeriodBetweenInDays(ExcelZeroDate, DateToConvert).Days

Thanks, will have a look at that.

RBS

RB Smissaert · Dec 31, 2023

klaus said:
The month abreviation depends on the language setting of the device.
English(United States) > Sep
English(Uniret Kingdom) > Sept
So, I suspect that on your device the language is English(United Kingdom) and it expects 'Sept' for September.
And then it fails in this line: If strEmisDate.Length <> 11 Then

You can test it that way:

B4X:

DateTime.DateFormat = "MM/dd/yyyy" Private mDateTicks As Long mDateTicks = DateTime.DateParse("09/21/2021") DateTime.DateFormat = "dd-MMM-yyyy" Private mDate As String mDate = DateTime.Date(mDateTicks) Log(mDate)

This thread from Erel shows some code that helped me out nicely:

[B4X] Set date format with locale

Cross platform code to change the date format with an explicit locale: Private Sub SetDateFormat(Language As String, Country As String, format As String) #if B4A or B4J Dim locale As JavaObject locale.InitializeNewInstance("java.util.Locale", Array(Language, Country)) Dim...

www.b4x.com

Avoids my clunky work-around dealing with the Sep month string.

RBS

Android Question Problem DateTime.DateParse if date contains Sep

RB Smissaert

Well-Known Member

emexes

Expert

RB Smissaert

Well-Known Member

emexes

Expert

aeric

Expert

RB Smissaert

Well-Known Member

RB Smissaert

Well-Known Member

klaus

Expert

emexes

Expert

RB Smissaert

Well-Known Member

emexes

Expert

klaus

Expert

ilan

Expert

RB Smissaert

Well-Known Member

RB Smissaert

Well-Known Member

emexes

Expert

RB Smissaert

Well-Known Member

RB Smissaert

Well-Known Member

[B4X] Set date format with locale

Similar Threads