Android Question Date Formatting

[FERNANDO SILVEIRA]

Hello guys,

I know this been discussed here quite a few times, but I still am unable to format an SQL date field as I desire, it keeps showing me the date as yyyy-mm-dd...

I tried the following code and the result is always the same: yyyy-mm-dd

DateTime.DateFormat = "dd/MM/yyyy"
wDataEvento = wRegistro(6)       ' <== database DATE field

DateTime.DateFormat = "EEE, d ' de ' MMMMM ' de ' yyyy"
wDataEvento = wRegistro(6)       ' <== database DATE field

What am I doing wrong?

BTW, do I have to explicitly code DateTime.DateFormat = something whenever I want a given format?
I was expecting a more straightforward thing like mydatefield = formatdate(db_field_date, "my format")... Any ideas?

 

[MarkusR]

after u set the format u can use

Dim lngDate As Long = DateTime.DateParse(sDate)

then the date is a number
if u change the format again you can convert it back into text

btnDay1.Text = DateTime.Date(lngDate)

 

[FERNANDO SILVEIRA]

after u set the format u can use

Dim lngDate As Long = DateTime.DateParse(sDate)

then the date is a number

Hi, Markus. Thanx for the feedback.
Not sure I got you...

1) You mean, something like ?

Dim wDataEvento As Long
DateTime.DateFormat = DateFormato2
wDataEvento = DateTime.DateParse(wRegistro(6))    ' <=== ERROR Unparseable date: "1961-08-29" (at offset 0)

2) I still didn't get what's the point in have a field type DATE if I have to change it to LONG in order to format it?

 

[MarkusR]

what type is wRegistro(6) ? did it have " at start and end inside the string?

B4J

    '--------------------------------
    Dim l As Long
    DateTime.DateFormat = "yyyy-mm-dd" ' "1961-08-29"
    Dim datestring As String = "1961-08-29"
    l = DateTime.DateParse(datestring)
    DateTime.DateFormat = "dd.mm.yyyy"
    Log(DateTime.Date(l))
    '--------------------------------

field type DATE

i believe b4a using a long and did not have a date/time type.
databases have much variants for store a date & time value.
which library do you use for your database access?
sql just means Structured Query Language

 

[FERNANDO SILVEIRA]

what type is wRegistro(6) ? did it have " at start and end inside the string?

B4J

    '--------------------------------
    Dim l As Long
    DateTime.DateFormat = "yyyy-mm-dd" ' "1961-08-29"
    Dim datestring As String = "1961-08-29"
    l = DateTime.DateParse(datestring)
    DateTime.DateFormat = "dd.mm.yyyy"
    Log(DateTime.Date(l))
    '--------------------------------

wRegistro(6) is a string resulting from a SELECT statement. One of the selected fields (wRegistro(6)) is stored as DATE in SQLITE.

Thank you again, besides I still don't believe so much code is required to format a date.

Anyway... As a local saying "that's what we have for today!"

 

[MarkusR]

i think you can format it direct in query
https://www.sqlite.org/lang_datefunc.html

http://www.sqlitetutorial.net/sqlite-date/

 

[FERNANDO SILVEIRA]

Thanx, Markus

I created this code and it is working just fine:

Log(FormatarData("1981-08-11", "yyyy-mm-dd", "dd/mm/yyyy"))
Log(FormatarData("1981-08-11", "yyyy-mm-dd", "dd de MMM de yyyy"))



'Returns a date converted from format1 to format2
'Element format accepted: dd, mm, yy, yyyy, MMM
'example: FormatarData("1981-08-11", "yyyy-mm-dd", "dd/mm/yyyy")
Sub FormatarData(data As String, formato1 As String, formato2 As String) As String
    Dim result As String = formato2
    If formato2.IndexOf("MMM") <> -1 Then
        Dim meses() As String = Array As String ("janeiro", "fevereiro", "março", "abril", "maio", "junho", "julho", "agosto", "setembro", "outubro", "novembro", "dezembro")
        result = result.Replace("MMM", meses((data.SubString2(formato1.IndexOf("mm"), formato1.IndexOf("mm")+2)) - 1))
    Else
        result = result.Replace("mm", data.SubString2(formato1.IndexOf("mm"), formato1.IndexOf("mm")+2))
    End If
    If formato1.IndexOf("yyyy") = -1 Then       
        result = result.Replace("yy", data.SubString2(formato1.IndexOf("yy"), formato1.IndexOf("yy")+2))
    Else                   
        result = result.Replace("yyyy", data.SubString2(formato1.IndexOf("yyyy"), formato1.IndexOf("yyyy")+4))
    End If
    result = result.Replace("dd", data.SubString2(formato1.IndexOf("dd"), formato1.IndexOf("dd")+2))
    Return result
End Sub

 

[Mahares]

I created this code and it is working just fine:

If you are happy with your code, that is fine, but your code looks verbose and complicated. If your date is: 1981-08-11 and you want to display it as: 11/08/1981, use the Substr function in SQLite. See the example below. It is different from Substring in B4A

Dim MyQuery As String
    MyQuery="SELECT  substr(MyDate,9,2) || '/' || substr(MyDate,6,2) || '/' || substr(MyDate,1,4) AS DT FROM Mytable "
    Cursor1=SQL1.ExecQuery(MyQuery)
    For i=0 To Cursor1.RowCount-1
        Cursor1.Position=i
        LogColor (Cursor1.GetString("DT"),Colors.Magenta)  '1981-08-11 will show up as: 11/08/1981
    Next

You can alternatively use the strfTime function in SQLite, but it is more complicated. Here is a link to it:
http://www.sqlite.org/lang_datefunc.html

 

[FERNANDO SILVEIRA]

If you are happy with your code, that is fine, but your code looks verbose and complicated. If your date is: 1981-08-11 and you want to display it as: 11/08/1981, use the Substr function in SQLite. See the example below. It is different from Substring in B4A

Dim MyQuery As String
    MyQuery="SELECT  substr(MyDate,9,2) || '/' || substr(MyDate,6,2) || '/' || substr(MyDate,1,4) AS DT FROM Mytable "
    Cursor1=SQL1.ExecQuery(MyQuery)
    For i=0 To Cursor1.RowCount-1
        Cursor1.Position=i
        LogColor (Cursor1.GetString("DT"),Colors.Magenta)  '1981-08-11 will show up as: 11/08/1981
    Next

You can alternatively use the strfTime function in SQLite, but it is more complicated. Here is a link to it:
http://www.sqlite.org/lang_datefunc.html

Thanx, Mahares

I'll give it a try.