If I run the following Oracle SQL query:
SELECT foo_number
FROM bar_log;
I have records that look like this:
20240621.16^123ABCD^SEARCH_PROFILE^AB1C3^9999
The first string (20240621.16) is a timestamp (YYYYMMDD.HH); the hour is reflected in 24-hour format, but hours with single digits do not have a leading zero. I would like to take the current date/time, convert it to reflect this timestamp format as a variable and use the variable in a WHERE clause to find records that match. What would be the best approach to accomplish this?
You can use to_char()
to convert a date/time to a string. Usually the HH24
format element gives leading zeros, which you don't want; but if you precede that element with the FM
format modifier it does not.
Running now at 11pm, that gives '23' for the hour; in an hour's time it will give '0' for midnight, not '00.
select to_char(sysdate, 'YYYYMMDD.FMHH24') from dual
TO_CHAR(SYSDATE,'YYYYMMDD.FMHH24') |
---|
20240621.23 |
select to_char(sysdate + 1/24, 'YYYYMMDD.FMHH24') from dual
TO_CHAR(SYSDATE+1/24,'YYYYMMDD.FMHH24') |
---|
20240622.0 |
select to_char(sysdate + 6/24, 'YYYYMMDD.FMHH24') from dual
TO_CHAR(SYSDATE+6/24,'YYYYMMDD.FMHH24') |
---|
20240622.5 |
You can then use that however you need:
...
where foo_number like to_char(sysdate, 'YYYYMMDD.FMHH24') || '^%'
Or even simpler, embedding the fixed characters in the format so you don't need to concatenate:
...
where foo_number like to_char(sysdate, 'YYYYMMDD.FMHH24"^%"')