Page 1 of 1

MySQL date format

PostPosted: Sun Jun 08, 2014 5:24 am
by ebear
Hello All,

Til now all my DB application are based on MS-SQL. Now I have for a specific application the need to use MySQL.

Almost everything works fine, connection, retrieving of data, data inserts etc.
The only think that I cannot archive is to get a valid date insert into the MySQL database.
I found on this forum some messages about the same problem, but a real solution never was given.

The fields in the MySQL database are of the type "Date"

I use a date selector with the following script:

Code: Select all
dbpPopupDateSelector "Left=483;Top=463;InitialDate=;DateFormat=Default" "[EdBsa.Licenses.StartDate]"


I changed the DateFormat already in several ways YYYY-MM-DD etc etc. Following the MySQL documentation, the input should be YYYY-MM-DD etc.
Again input direct in the database of a YYYY-MM-DD works without a problem, but via Neobook the input fails with a "invalid date format" message.

Anyone....

Best regards,

Eric

Re: MySQL date format

PostPosted: Sun Jun 08, 2014 8:06 am
by Gaev
Eric:

I tried this ...
Code: Select all
dbpPopupDateSelector "Left=200;Top=200;InitialDate=;DateFormat=YYYY-MM-DD" "[popupDate]"
... and setup a SimpleText object to view the result.

On my machine, it displayed the result as expected ... so, first see if you can replicate the result on your machine.

If it works as expected, you can try one of these alternative solutions ...

1) use DateFormat=YYYYMMDD

Perhaps the result (e.g. 2014-06-08) is being treated like an expression ... and the value of 2014 minus 6 minus 8 is being passed to the database engine ... MySQL accepts the YYYYMMDD format

2) follow the dbpPopupDateSelector command (as specified above) with ...
Code: Select all
SetVar "[EdBsa.Licenses.StartDate]" "![popupDate]"

... or even ...

SetVar "[EdBsa.Licenses.StartDate]" "!#[popupDate]#"



Good luck.

Re: MySQL date format

PostPosted: Mon Jun 09, 2014 8:47 am
by ebear
Hello Gaev,all,

Okay I didn't really solve the problem I described in my start message (Direct input from a dbpPopupDateSelector to the Table record). Whatever I did with the date format, it always failed.
The problem seems the same as I had before with MS-SQL, the MS-SQL Client 10 solved this problem.

But I redesigned my code to have the dates correct inserted in the Table.

First the dbpPopupDateSelector puts the dates in a variable:
Code: Select all
dbpPopupDateSelector "Left=180;Top=87;InitialDate=;DateFormat=yyyymmdd" "[StartDate]"


Then at "Save" the data is "INSERTED" into the database:
Code: Select all
dbpExecSQL "EdBsa" "INSERT INTO Licenses (Author, CustomerID, LicKey, StartDate, EndDate, Qty, Memo) VALUES ('[User]', [EdBsa.Customers.ID], '[Key]', [StartDate], [EndDate], [Qty], '[Memo]')" ""


I not that glad with the date as YYYYMMDD instead of YYYY-MM-DD, but otherwise this solves the issue.

Best regards and thanks,

Eric

Re: MySQL date format

PostPosted: Mon Jun 09, 2014 9:05 am
by Gaev
Eric:
I not that glad with the date as YYYYMMDD instead of YYYY-MM-DD, but otherwise this solves the issue.
Try this ...
Code: Select all
dbpPopupDateSelector "Left=180;Top=87;InitialDate=;DateFormat=yyyy-mm-dd" "[StartDate]"
... then, when you want to Save the record ...
Code: Select all
StrReplace "![StartDate]" "!-" "" "[StartDate2]" ""

dbpExecSQL "EdBsa" "INSERT INTO Licenses (Author, CustomerID, LicKey, StartDate, EndDate, Qty, Memo) VALUES ('[User]', [EdBsa.Customers.ID], '[Key]', [StartDate2], [EndDate], [Qty], '[Memo]')" ""

Re: MySQL date format

PostPosted: Mon Jun 09, 2014 9:37 am
by ebear
Thanks Geav,

Simple but functional!!!!!! Great. Of cause it was only a cosmetic issue...
I'm fairly experienced in SQL (MS-SQL), but MySQL has some nuances I have to learn.

Regards,

Eric