Home Date and time stamp comparison with today in ODBC in Excel?
Reply: 0

Date and time stamp comparison with today in ODBC in Excel?

user1489
1#
user1489 Published in June 19, 2018, 6:17 pm

Question

I'm querying a DataBase via Excel ODBC.

I can compare the field with an hard-coded date in my Excel's ODBC query with :

BOMD.BOMENDDAT_0 > {ts '2018-05-05 00:00:00'}

But I can't seem to make a proper comparison with the present date :

BOMD.BOMENDDAT_0 > {ts SYSDATETIME()}
DATEDIFF(d, SYSDATETIME(), BOMD.BOMENDDAT_0) > 0

Any suggestion ?


Context

I'm trying to build a query that select only records where the date of today is between :

  • a start date (BOMD.BOMSTRDAT_0)
  • an end date (BOMD.BOMENDDAT_0)

Those dates :

  • are in a special format, they look like this in Excel : '26/02/2018 00:00:00'
    (French format, Day Month Year)
  • can be filled or not
  • 0 seems to be '00/01/1900 00:00:00'

The whole query look like this :

SELECT BOMD.BOMQTY_0, BOMD.UPDDAT_0, BOMD.BOMSTRDAT_0, BOMD.BOMENDDAT_0
FROM myDB.BOMD BOMD
WHERE BOMD.UPDDAT_0 > {ts '2018-01-01 00:00:00'}
AND (
    (BOMD.BOMSTRDAT_0 < {ts SYSDATETIME()})
    OR BOMD.BOMSTRDAT_0 = 0
    OR BOMD.BOMSTRDAT_0 is null
    )
AND (
    (BOMD.BOMENDDAT_0 > {ts SYSDATETIME()})
    OR BOMD.BOMENDDAT_0 = 0
    OR BOMD.BOMENDDAT_0 is null
    )
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.330035 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO