Date and time

[Mike Hughes]

The DB table which I'm quering has a field called DT_QUERIED and the data returned from this field is returned as 5/29/2009 9:29:56 AM
I'm quering for records from yesterday.

When I run this query I get no results (I know there is data there)

SELECT
NOLDBA_USER_AUDIT.USERID,
NOLDBA_USER_ACCOUNTS.FIRST_NAME, NOLDBA_USER_ACCOUNTS.LAST_NAME,
NOLDBA_USER_AUDIT.ID_CASE,
NOLDBA_USER_AUDIT.ID_SCREEN,
NOLDBA_USER_AUDIT.DT_QUERIED

FROM
NOLDBA_USER_AUDIT INNER JOIN NOLDBA_USER_ACCOUNTS ON NOLDBA_USER_AUDIT.USERID=NOLDBA_USER_ACCOUNTS.USERID

WHERE NOLDBA_USER_AUDIT.DT_QUERIED =NOW()-1

Can someone please show me how this can be fixed

Thanks, MIke

 

[jdraw]

Mike,

I think Now -1 will give you exactly 24 hours ago with hours and minutes.
If it is (Now) 6/8/2011 7:49:18 AM , then
Now-1 will result in 6/7/2011 7:49:18 AM

I think you want everything from midnight 6/7/2011 until 6/8/2011, so I would not use "=". I'd try ">".

You might try

where X > Date -1 (untested) or


Where yourdate Between Date - 1 and Date

 

[Mike Hughes]

Between Date()-1 And Date()

Worked! Thanks for the help

Mike