CODEWITHSUNDEEP
sqloracle-database
Guzmicz
Guzmicz

Reputation: 31

ORACE How to get begin of the day from CURRENT_TIMESTAMP

I have a problem with solving this problem. I have to extract the IDs of data from a table that has been changed only today. My current solution is

SELECT DISTINCT id
FROM changes_table
WHERE valid_to > (SELECT TO_DATE(CURRENT_DATE, 'DD-MON-RR') FROM DUAL)

This works in BDeaver but not in my application, there I get "ORA-01843:"

So is it possible to use CURRENT_TIMESTAMP, but it take timestamp from now. Is it possible to change it to format DD-MON-RR with 00:00:00 time?

Or is there any other solution?

I can send only one SQL command.

Upvotes: 0

Views: 34

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521467

You may simply use SYSDATE here truncated to midnight:

SELECT DISTINCT id
FROM changes_table
WHERE valid_to > TRUNC(SYSDATE);  -- RHS is today at midnight

Note that the above should work whether valid_to be a date or a timestamp.

Upvotes: 2

Related Questions