Source language: Translate to:

MySQL date format

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

MySQL date format

Postby ebear » Sun Jun 08, 2014 5:24 am

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
http://www.lunarsoftware.eu - Lunar Software ERP database Development. Manufacturing processes, Document management, QMS, Stock management. Standalone solutions or connected to Helios Orange and other ERP systems.
User avatar
ebear
 
Posts: 270
Joined: Wed Jan 18, 2012 3:12 am
Location: Vrchlabi - Czech republic

Re: MySQL date format

Postby Gaev » Sun Jun 08, 2014 8:06 am

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.
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: MySQL date format

Postby ebear » Mon Jun 09, 2014 8:47 am

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
http://www.lunarsoftware.eu - Lunar Software ERP database Development. Manufacturing processes, Document management, QMS, Stock management. Standalone solutions or connected to Helios Orange and other ERP systems.
User avatar
ebear
 
Posts: 270
Joined: Wed Jan 18, 2012 3:12 am
Location: Vrchlabi - Czech republic

Re: MySQL date format

Postby Gaev » Mon Jun 09, 2014 9:05 am

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]')" ""
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: MySQL date format

Postby ebear » Mon Jun 09, 2014 9:37 am

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
http://www.lunarsoftware.eu - Lunar Software ERP database Development. Manufacturing processes, Document management, QMS, Stock management. Standalone solutions or connected to Helios Orange and other ERP systems.
User avatar
ebear
 
Posts: 270
Joined: Wed Jan 18, 2012 3:12 am
Location: Vrchlabi - Czech republic


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 3 guests