SQL实现日期的一位数表达

由于某些制约因素导致barcode 不能超过一定的长度，这就要求在日期上做些改动。

如上图特殊日期格式：

年只保留后两位；

月份保留一位，超出一位则用字母代替；

日同样保留一位，超出一位则用字母代替；

 

--年很容易实现，因为系统默认支持‘YY’格式：

SELECT TO_CHAR(sysdate,''YY'') Years FROM dual;

 

--月份实现方式如下：

SELECT
  CASE
      WHEN to_char(sysdate,'MM')='10' THEN 'A'
      WHEN to_char(sysdate,'MM')='11' THEN 'B'
      WHEN to_char(sysdate,'MM')='12' THEN 'C'
      ELSE to_char(TO_NUMBER(to_char(sysdate,'MM'))) END Alpha_numeric_month,
FROM dual;

 

--日 实现方式如下：

SELECT

CASE
      WHEN to_char(sysdate,'dd')='10' THEN 'A'
      WHEN to_char(sysdate,'dd')='11' THEN 'B'
      WHEN to_char(sysdate,'dd')='12' THEN 'C'
      WHEN to_char(sysdate,'dd')='13' THEN 'D'
      WHEN to_char(sysdate,'dd')='14' THEN 'E'
      WHEN to_char(sysdate,'dd')='15' THEN 'F'
      WHEN to_char(sysdate,'dd')='16' THEN 'G'
      WHEN to_char(sysdate,'dd')='17' THEN 'H'
      WHEN to_char(sysdate,'dd')='18' THEN 'I'
      WHEN to_char(sysdate,'dd')='19' THEN 'J'
      WHEN to_char(sysdate,'dd')='20' THEN 'K'
      WHEN to_char(sysdate,'dd')='21' THEN 'L'
      WHEN to_char(sysdate,'dd')='22' THEN 'M'
      WHEN to_char(sysdate,'dd')='23' THEN 'N'
      WHEN to_char(sysdate,'dd')='24' THEN 'O'
      WHEN to_char(sysdate,'dd')='25' THEN 'P'
      WHEN to_char(sysdate,'dd')='26' THEN 'Q'
      WHEN to_char(sysdate,'dd')='27' THEN 'R'
      WHEN to_char(sysdate,'dd')='28' THEN 'S'
      WHEN to_char(sysdate,'dd')='29' THEN 'T'
      WHEN to_char(sysdate,'dd')='30' THEN 'U'
      WHEN to_char(sysdate,'dd')='31' THEN 'V'
      ELSE to_char(TO_NUMBER(to_char(sysdate,'dd'))) END Alpha_numeric_day,
FROM dual;

 

--将年月日汇总得

SELECT

  TO_CHAR(sysdate,''YY'') Years,

  CASE
      WHEN to_char(sysdate,''MM'')=''10'' THEN ''A''
      WHEN to_char(sysdate,''MM'')=''11'' THEN ''B''
      WHEN to_char(sysdate,''MM'')=''12'' THEN ''C''
      ELSE to_char(TO_NUMBER(to_char(sysdate,''MM''))) END ANMON,
  CASE
      WHEN to_char(sysdate,''dd'')=''10'' THEN ''A''
      WHEN to_char(sysdate,''dd'')=''11'' THEN ''B''
      WHEN to_char(sysdate,''dd'')=''12'' THEN ''C''
      WHEN to_char(sysdate,''dd'')=''13'' THEN ''D''
      WHEN to_char(sysdate,''dd'')=''14'' THEN ''E''
      WHEN to_char(sysdate,''dd'')=''15'' THEN ''F''
      WHEN to_char(sysdate,''dd'')=''16'' THEN ''G''
      WHEN to_char(sysdate,''dd'')=''17'' THEN ''H''
      WHEN to_char(sysdate,''dd'')=''18'' THEN ''I''
      WHEN to_char(sysdate,''dd'')=''19'' THEN ''J''
      WHEN to_char(sysdate,''dd'')=''20'' THEN ''K''
      WHEN to_char(sysdate,''dd'')=''21'' THEN ''L''
      WHEN to_char(sysdate,''dd'')=''22'' THEN ''M''
      WHEN to_char(sysdate,''dd'')=''23'' THEN ''N''
      WHEN to_char(sysdate,''dd'')=''24'' THEN ''O''
      WHEN to_char(sysdate,''dd'')=''25'' THEN ''P''
      WHEN to_char(sysdate,''dd'')=''26'' THEN ''Q''
      WHEN to_char(sysdate,''dd'')=''27'' THEN ''R''
      WHEN to_char(sysdate,''dd'')=''28'' THEN ''S''
      WHEN to_char(sysdate,''dd'')=''29'' THEN ''T''
      WHEN to_char(sysdate,''dd'')=''30'' THEN ''U''
      WHEN to_char(sysdate,''dd'')=''31'' THEN ''V''
      ELSE to_char(TO_NUMBER(to_char(sysdate,''dd''))) END ANDAY

FROM dual;

 

显示结果（2015年1月23日）：

Years   ANMON   ANDAY

15         1               N