Ask Your Question
0

Date fields are driving me mad

asked 2017-09-11 23:13:56 +0200

Neil-B gravatar image

updated 2017-09-11 23:17:39 +0200

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

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2017-09-11 23:58:39 +0200

Ratslinger gravatar image

updated 2017-09-12 06:28:39 +0200

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:

image description

Base table before update:

image description

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:

image description

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:

image description

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

edit flag offensive delete link more

Comments

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

Neil-B gravatar imageNeil-B ( 2017-09-12 00:50:22 +0200 )edit

@Neil-B See edit in answer.

Ratslinger gravatar imageRatslinger ( 2017-09-12 02:35:03 +0200 )edit

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

Neil-B gravatar imageNeil-B ( 2017-09-12 18:35:20 +0200 )edit

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.

Ratslinger gravatar imageRatslinger ( 2017-09-12 18:49:45 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-09-11 23:13:56 +0200

Seen: 560 times

Last updated: Sep 12 '17