OraQA

The following is an interesting problem posted by mathforum.org:

Using the abbreviation date.month.year (the last two digits of the year), what are the 2 palindromic dates (of any number of digits) closest together in the 1900s?

———————————-SQL Solution——————————-

SELECT date_str, next_date, min_diff_days
FROM
(SELECT min(diff_days) over ( ) min_diff_days, date_str, next_date, diff_days
 FROM
 (SELECT lead(date_str) over (order by pal_date) next_date, date_str,
         lead(pal_date) over (order by pal_date) - pal_date  as diff_days
  FROM
  (SELECT to_char(to_date('01/01/1900','DD/MM/YYYY')-1+ LEVEL,'fmDD.MM.fmYY') date_str,
          to_date('01/01/1900','DD/MM/YYYY')-1  + LEVEL pal_date
   FROM dual
   CONNECT BY to_date('01/01/1900','MM/DD/YYYY')+LEVEL <= to_date('01/01/2000','MM/DD/YYYY')
  )
  WHERE replace(date_str,'.') =
       utl_raw.cast_to_varchar2(utl_raw.reverse(utl_raw.cast_to_raw((replace(date_str,'.')))))
 )
)
WHERE diff_days = min_diff_days;

DATE_STR     NEXT_DAT       MIN_DIFF_DAYS
——–                    ——–        ————-
29.8.92         2.9.92                 4

Elapsed: 00:00:00.41

Filed under SQL. Follow any responses to this entry through this RSS 2.0 feed.