由于某些制约因素导致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