Date fields are driving me mad

[Neil-B]

Hi All,

As the title says, date fields are between Calc and MYSQL are driving around the twist,

This question could fall in the gap between the two of them.

what I have is a spread sheet for which part cells A1 and A2 are formatted as date “D MMM YYYY”

using a macro I grab the cell values like this

inv_date = Sheet1.getCellRangeByName("A1").value
paid_date = Sheet1.getCellRangeByName("A2").value

I have tried setting the data type to date

  Dim inv_date as date

but this just errors with

BASIC runtime error.
Property or method not found: date.

if I use the .value it does display the correct date

  print inv_date

from there I export the data to mysql

 strSQL6 = " update  work.invoices_payable set inv_date = '" & inv_date & "' where entry_id = '" & entry_number & "'"

now for the MYSQL (Via Base) bit, my field name is also called inv_date,

if I format this as a “date” field

this display “3 jan 0001” in base no matter what date i put in the calc cell

if the "inv_date is formated as “INT” it does display the correct date but i cannot query the date with

select date(inv_date) from work.invoices_payable 

as this just give me a NULL value.

I need to be able to query the dates for this to work for me

Any sugestions?

Neil

[Ratslinger]

The problem you are having is because of the format used in the Update statement. Dates in most databases are stored in YYYY-MM-DD format regardless of anything else. So if you convert the date:

    convertedDate = Format(inv_date, "YYYY-MM-DD")

and use the converted field name in your Update statement, you problem should go away.

Edit:

You cannot use the conversion in the select statement. Here is a proven test. Calc sheet (excuse the garbage - do a LOT of testing here) using cell A10:

Base table before update:

Macro code (again, just grabbed an existing routine & modified it):

Sub UpdateSQL
        Dim Context as Object
        Dim Db as Object
        Dim Conn as Object
        Dim Stmt as Object
        Dim strSQL as String
    oSheet = ThisComponent.Sheets.getByName("Sheet14")
    inv_date = oSheet.getCellRangeByName("A10").value
    TestDate = Format(inv_date, "YYYY-MM-DD")
    rem Run an SQL command on a table in LibreOffice Base
    Context = CreateUnoService("com.sun.star.sdb.DatabaseContext")
    rem URL of database (using a registered DB name)
    dbNAME = "REGISTERED_NAME"
    Db = Context.getByName(dbNAME)
    rem connect to the database
    Conn = Db.getConnection("USER","PASSWORD") 
    rem create statement object
    Stmt = Conn.createStatement()
    rem compose SQL statement
    rem insert date values into table
    strSQL = "UPDATE TABLE3 SET MYDATE = '" & TestDate & "' WHERE NAME = 'Input 101'"
    rem execute SQL statement
    Stmt.executeUpdate(strSQL)
    rem close the databae connection
    Conn.close()
    rem notify the user
    msgbox("table updated")
End Sub

Ran the code and this is result:

Note that the record updated was the second record - can match to macro. Been working with dates in many different DB’s. This seems to be one common problem people have. The format for the update is key.

Another edit:

Just to complete, here is a query:

If this answers your question please click on the (upper left area of answer).

[Neil-B]

Hi Ratslinger,
If I change a cell with todays date in it to a number i get 42989,
if I enter 42989 into a date field it displays todays date as normal regardless of the format. (spreadsheet veiw)
If I then change the column format to numbers it displays my 42989 again, but the record via my macro on the same field display -693959 in number format and 0001-01-03 regardless of which dates i have used.

I have run select
Format(inv_date, “YYYY-MM-DD”) from invoices_payable with no luck either
Nei

[Ratslinger]

@Neil-B See edit in answer.

[Neil-B]

Hi Ratslinger,
I added the TestDate = Format(inv_date, “YYYY-MM-DD”) from your macro into mine for all the dates fields I have and they work perfectly for me now, I can also query them and get the results I was expecting. Its been a very frustrating time working with dates even just getting them to display properly was a challenge.
Thank you for taking the time to help me threw this, much appreciated.

Regards Neil

[Ratslinger]

Glad you got it working. Just keep in mind with dates, internal format vs displayed format. Conversions happen behind the scenes which many are not aware of.