Android Question Problem DateTime.DateParse if date contains Sep

R

RB Smissaert

Well-Known Member
Licensed User
Longtime User
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
 
Sort by date Sort by votes
R

RB Smissaert

Well-Known Member
Licensed User
Longtime User
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
Click to expand...
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
 
Upvote 0
E

emexes

Expert
Licensed User
RB Smissaert said:
doesn't look very nice:
Click to expand...
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

nevergiveup320.jpg
 
Last edited:
Upvote 0
R

RB Smissaert

Well-Known Member
Licensed User
Longtime User
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
Click to expand...
Agree that work-around looks bad, but it does work.

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

RBS
 
Upvote 0
klaus

klaus

Expert
Licensed User
Longtime User
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)
 
Upvote 0
R

RB Smissaert

Well-Known Member
Licensed User
Longtime User
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)
Click to expand...
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
 
Upvote 0
klaus

klaus

Expert
Licensed User
Longtime User
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.
 
Upvote 0
ilan

ilan

Expert
Licensed User
Longtime User
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!
 
Upvote 0
R

RB Smissaert

Well-Known Member
Licensed User
Longtime User
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.
Click to expand...
>> 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
 
Upvote 0
R

RB Smissaert

Well-Known Member
Licensed User
Longtime User
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!
Click to expand...
I am parsing text files and have no control over these files.
Dates are stored in the SQLite DB as Integer.

RBS
 
Upvote 0
E

emexes

Expert
Licensed User
RB Smissaert said:
need to convert dates in the format dd-MMM-yyyy into Excel dates
Click to expand...

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
 
Upvote 0
R

RB Smissaert

Well-Known Member
Licensed User
Longtime User
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
Click to expand...
Thanks, will have a look at that.

RBS
 
Upvote 0
R

RB Smissaert

Well-Known Member
Licensed User
Longtime User
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)
Click to expand...
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 www.b4x.com

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

RBS
 
Upvote 0
You must log in or register to reply here.
Top