Android Question B4A/SQLite & ISO8601 dates

[Alwinl]

I am exporting data from SQL Server to store in a text field in SQLite on my device using this sql:

CONVERT(VARCHAR, MY_DATETIME, 127) ( where 127 denotes 'yyyy-MM-ddTHH:mm:ss:SSSZ')

All good, SQLite understand this just fine as per the docs. My problem is when I want to use this date in B4A code because:

1. If I set the DateFormat to 'yyyy-MM-ddTHH:mm:ss:SSSZ' I get an error saying the 'T" is invalid

2. If I set the DateFormat to 'yyyy-MM-dd HH:mm:ss:SSSZ' the format is set fine but then I cannot DateParse using this (I am guessing because my data has the 'T' present)


Any ideas on how best to handle this, especially since the 'T' should be present as per ISO8601?

 

[Mahares]

What if you try something like this:

Dim strDate As String
DateTime.DateParse(strDate.Replace("T"," "))

 

[Alwinl]

Thanks - I'm sure that will work, although there could also be 'T' in the timezone bit so I'll need to factor that in. I was just hoping to handle ISO8601 dates natively without having to do any pre-formatting

 

[rbsoft]

Here is a link to the SQLite DateTime strings and functions:
http://www.sqlite.org/lang_datefunc.html

Rolf

 

[Alwinl]

Thanks - I had been to that page previously and it's what prompted me to use ISO8601. Anyway, due to another issue in SQL Server when I export my data to SQLite listed here:
http://social.msdn.microsoft.com/Fo...onvert-datetime-to-iso8601-drops-milliseconds

I had to have quite a bit of extra processing so here is a function that is reasonably strong

'Convert an ISO8601 date string (yyyy-MM-ddTHH:mm:ss:SSSZ) to a date (long)
'Returns 0 on error
Sub StrToDateISO8601(sDate As String) As Long
    Dim lRet As Long
    Dim s As String
    Dim sFormat As String
    Try
        'Get rid of the 'T'
        s = sDate.Trim
        s = s.SubString2(0,10) & " " & s.SubString(11)
        If s.Length = 19 Then
            sFormat = "yyyy-MM-dd HH:mm:ss"
        Else If s.Length = 23 Then
            sFormat = "yyyy-MM-dd HH:mm:ss:SSS"
        Else
            sFormat = "yyyy-MM-dd HH:mm:ss:SSSZ"
        End If
        DateTime.DateFormat = sFormat       
        lRet = DateTime.DateParse(s)
    Catch
        lRet = 0
    End Try
    Return lRet
End Sub