ODBC SQL query - curdate() not working

[Squiggs]

Hello all,

I have an application running as a service on a server at our office and it established a connection to a Progress Database using ODBC when the service was started last week (Oct 9th). I'm running across the connection to get a list of records from yesterday and today using "curdate() - 1". The query worked fine the first day and in my tests, however now that its been running for a couple of days, it appears to be returning all the records since Oct 8th (1 day prior to the last server restart). Is there an equivalent function that I would be able to run that might accomplish the same thing and is not affected by this issue?

Right now I'm considering just having the application build a custom querystring with the actual current date rather than the sql function, however I'll probably have to strip the time off.

Has anyone experienced this or know a safer method of getting records from yesterday?

Full query returning a week of records (instead of 1 day as expected):

select formtype + "form-no", scannedOn, 'NOT PACKAGED'
   from pub.scan
   where "ar-entity" = 'ae'
   and "scannedon" >= (curdate() - 1)
   and scanid in (70, 92)
   and "form-no" not in (
      select "form-no"
         from pub.scan
         where "ar-entity" = 'ae'
         and "scannedon" >= (curdate() - 1)
         and scanid in (141, 145, 146)
      union select "wo-no" as "form-no"
         from pub."wo-header"
         where "ar-entity" = 'ae'
         and "in-entity" = 'a'
         and "order-no" in (
            select "form-no"
               from pub.scan
               where "ar-entity" = 'ae'
               and formtype = 'o'
               and "scannedon" >= (curdate() - 1)
               and scanid in (141, 145, 146)))
union select formtype + "form-no", scannedOn, 'PACKAGED'
   from pub.scan
   where "ar-entity" = 'ae'
   and "scannedon" >= (curdate() - 1)
   and scanid in (70, 92)
   and "form-no" in (
      select "form-no"
         from pub.scan
         where "ar-entity" = 'ae'
         and "scannedon" >= (curdate() - 1)
         and scanid in (141, 145, 146)
      union select "wo-no" as "form-no"
         from pub."wo-header"
         where "ar-entity" = 'ae'
         and "in-entity" = 'a'
         and "order-no" in (
            select "form-no"
               from pub.scan
               where "ar-entity" = 'ae'
               and formtype = 'o'
               and "scannedon" >= (curdate() - 1)
               and scanid in (141, 145, 146)))

 

[Casper]

Try SYSDATE() instead of CURDATE().

Casper.

 

[Squiggs]

SysDate() did infact work. Thanks for your help.

 

[Casper]

FYI There is an issue that curdate() and now() are being cached by the _sqlsrv2 proces. As a workaround you should use sysdate() and systimestamp().

Casper.