Search code examples
sqloracle

How to reformat current date/time into a variable for use in a "where" clause

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?

Solution

  • 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

    fiddle

    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"^%"')

    fiddle