Hi there,
I am fairly a newby in dev space. I have a piece of code (VBA) that runs a query to export data from SQL database into excel via Ms query (ODBC). The code works fine when having static time stamp like this:
"WHERE (vwTransactionData.CreateDateTime={ts '2016-01-01 00:00:00'}-0.375)")....0.375=9hrs and it work great with this
But i would like the code to fire up the query at 7 in the morning and to pull data backwards to 9 hrs e.g
"WHERE (vwTransactionData.CreateDateTime={ts 'now()'}-0.375)"), however the sysntax is not correct as the query can not run.
Please help
I don't know exactly about ODBC, but {ts 'now()'} is definitely wrong, this tries to convert the literal 'now()' into a timestamp, imho this should be {fn now()} instead.
Hi Dieter,
thank you so much for helping, {fn now() function did pick up the table headers or columns but not the fields or data in the columns:
Shipment | SheetNumber | Barcode | Qty | Unit | Picker_Name | Surname | Picker Barcode | Type | CreateDateTime | StartTime | EndTime | Duration | Status | Completed |
I also tried this :
{fn TIMESTAMPADD( SQL_TSI_DAY, -80, CURRENT_TIMESTAMP)} to try and pull 80 days from current date and it still returned column names without the data below them,,
could you please enlighten as to what i am missing?
thank you for helping
{fn TIMESTAMPADD( SQL_TSI_DAY, -80, CURRENT_TIMESTAMP)}
select * from TabFoo where X = {fn TIMESTAMPADD( SQL_TSI_DAY, -80, {fn CURRENT_TIMESTAMP})}
Note that the CURRENT_TIMESTAMP must be wrapped with FN escape sequence also. The ODBC driver will convert it to:
select * from TabFoo where X = CAST (-80 AS INTERVAL DAY(4)) + CAST (CURRENT_TIMESTAMP AS TIMESTAMP)