All Forums Connectivity
Blake001 2 posts Joined 02/16
11 Feb 2016
ODBC ts time stamp for now()

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 4628 posts Joined 11/04
12 Feb 2016

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.

Dieter

Blake001 2 posts Joined 02/16
13 Feb 2016

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 346 posts Joined 09/06
18 Feb 2016
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)

 

You must sign in to leave a comment.