1.日期格式中的“J”和“jsp”表示什么意思？
select to_char(to_date(222,'J'),'jsp') from dual;
select to_char(to_date(222,'J'),'JSP') from dual;
select to_char(to_date(222,'J'),'Jsp') from dual; 2.计算08年奥运会(2008-08-08 20:00:00) 距离当前时间已过了多少天多少小时多少分多少秒? 求SQL!
select to_char(to_date(222,'J'),'jsp') from dual;
select to_char(to_date(222,'J'),'JSP') from dual;
select to_char(to_date(222,'J'),'Jsp') from dual; 2.计算08年奥运会(2008-08-08 20:00:00) 距离当前时间已过了多少天多少小时多少分多少秒? 求SQL!
SQL> SELECT to_char(to_date(222, 'J'), 'jsp') FROM dual;TO_CHAR(TO_DATE(222,'J'),'JSP'
---------------------------------------------------------------------------
two hundred twenty-twoSQL> SELECT to_char(to_date(222, 'J'), 'JSP') FROM dual;TO_CHAR(TO_DATE(222,'J'),'JSP'
---------------------------------------------------------------------------
TWO HUNDRED TWENTY-TWOSQL> SELECT to_char(to_date(222, 'J'), 'Jsp') FROM dual;TO_CHAR(TO_DATE(222,'J'),'JSP'
---------------------------------------------------------------------------
Two Hundred Twenty-TwoSQL> --2、时间差
SQL> WITH tt AS
2 (SELECT SYSDATE - to_date('2008-08-08 20:00:00', 'yyyy-mm-dd hh24:mi:ss') dt FROM dual)
3 SELECT '奥运会距今已过:' || trunc(dt) || '天' || trunc((dt - trunc(dt)) * 24) || '小时' ||
4 trunc((dt * 24 - trunc(dt * 24)) * 60) || '分' || trunc((dt * 1440 - trunc(dt * 1440)) *
60) || '秒' diff
5 FROM tt;DIFF
--------------------------------------------------------------------------------
奥运会距今已过:629天3小时10分22秒SQL>
Convert Numbers to Wordsby Brian Membrey, The PaperBag Software Company Pty LtdI guess a mundane exercise that most programmers cop at some stage is having to convert a number (123) into a text equivalent (ONE HUNDRED AND TWENTY THREE) - for cheques, group certificates, etc. Probably the code involved a loop stripping out the numerals and then applying a value according to the relative position within the overall value.Although it winds a strange path via date functions, SQL*Plus actually provides a mechanism for automating much of this process. Executing the following :
SELECT TO_CHAR ( TO_DATE ( TO_CHAR ( 103465, '99999999999') , 'J'), 'JSP') FROM dual;
returns a value of ONE HUNDRED THREE THOUSAND FOUR HUNDRED SIXTY-FIVEIf we break the statement into each component function, then what happens is :
the inner TO_CHAR simply converts the number (which would probably be a numeric variable in practice) to CHAR so some magic can happen ... the TO_DATE converts the CHAR using the J (Julian day) format. (the Julian day is the number of days since January 1, 4712BC, which is when SQL*Plus was invented), having established the date value, we then convert that date back to a Julian day. Because the TO_CHAR in this case is used in DATE context, we can use the J mask to duplicate the original value, and append the SP (spell) format mask. 'Spell" does exactly that - it converts the number to words, hence the string value above.
SP can be used in a number of situations. For example, if SYSDATE is 26-AUG-98, then : SELECT TO_CHAR ( SYSDATE, 'DdSp') FROM dual; -- spells the day as Twenty-Six,
and
SELECT TO_CHAR ( SYSDATE, 'DDSPTH') FROM dual; --returns TWENTY-SIXTH
Some simple manipulations can be included with the base conversion to cover floating numbers or currencies (email [email protected] for source), eg. 103465.27 becomes ONE HUNDRED AND THREE THOUSAND FOUR HUNDRED AND SIXTY-FIVE DOLLARS AND TWENTY-SEVEN CENTS.One covenant however : if in your mad appreciation of this trivia you want to send me a cheque for more than $5,373,484.00, then you'll have to write it manually, or send more than one cheque!SQL*Plus restricts Julian days to between 1 and 5373484, which won't be a problem for most applications, but should be borne in mind before using the technique in anger.5373484 represents 31-Dec-9999, so this may be Oracle's way of introducing us to a Year 10K problem!
oracle QQ群：54775466
期待您的一起探讨
欢迎爱好者入群学习
select extract(DAY from dt) "天",
extract(hour from dt) "小时",
extract(minute from dt) "分",
extract(second from dt) "秒"
from (
select systimestamp - to_date('2008-08-08 20:00:00', 'yyyy-mm-dd hh24:mi:ss') dt
from dual
)