ODBC ts time stamp for now()

[Blake001]

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

[dnoeth]

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.

[Blake001]

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)}

[NetFx]

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)