B4J Question MySQL Date conversion

Hi Guys,

I currently have data stored in a MySQL table as '2018-07-31 00:00:00', however, my DatePicker populates a variable as '31-07-2018' and fails to pull data through.

When is the best time to convert the date and how?

On DatePicker_ValueChanged or inside the SQL when I look up the date field in the MySQL table?

Basically, when they click the DatePicker I want to store that date in a variable as 'yyyy-mm-dd' rather than 'dd/mm/yyyy'.
 

marcick

Well-Known Member
Licensed User
From DatePicker, you read the date as ticks.
So, read the value, change the dateformat to "yyyy-MM-dd HH:mm:ss", get the datetime string and store in MySql.
Experts says that if you store the date in MySql as ticks, you get free of any trouble. But I prefer a string.
 
From DatePicker, you read the date as ticks.
So, read the value, change the dateformat to "yyyy-MM-dd HH:mm:ss", get the datetime string and store in MySql.
Experts says that if you store the date in MySql as ticks, you get free of any trouble. But I prefer a string.
Thanks Marcick.

I am quite new to this. Do you have of an example of what you mentioned above please? Or point me in the right direction?
 

marcick

Well-Known Member
Licensed User
B4X:
Sub DatePicker1_ValueChanged (Value As Long)
    Dim CurrentFormat As String=DateTime.DateFormat
    DateTime.DateFormat="yyyy-MM-dd HH:mm:ss"
    Dim MySqlDate As String=DateTime.Date(Value)
    Log(MySqlDate)
    DateTime.DateFormat=CurrentFormat
End Sub
 
B4X:
Sub DatePicker1_ValueChanged (Value As Long)
    Dim CurrentFormat As String=DateTime.DateFormat
    DateTime.DateFormat="yyyy-MM-dd HH:mm:ss"
    Dim MySqlDate As String=DateTime.Date(Value)
    Log(MySqlDate)
    DateTime.DateFormat=CurrentFormat
End Sub
Worked a treat thanks again :)
 
Top