]]>数据库日期函?/title>http://www.aygfsteel.com/sunjavaee/archive/2006/08/02/61412.html每天q步一点点每天q步一点点Wed, 02 Aug 2006 14:51:00 GMThttp://www.aygfsteel.com/sunjavaee/archive/2006/08/02/61412.htmlhttp://www.aygfsteel.com/sunjavaee/comments/61412.htmlhttp://www.aygfsteel.com/sunjavaee/archive/2006/08/02/61412.html#Feedback0http://www.aygfsteel.com/sunjavaee/comments/commentRss/61412.htmlhttp://www.aygfsteel.com/sunjavaee/services/trackbacks/61412.htmlselect current_timestamp from dual select sysdate from dual select to_date('2005-08-19 12:59:59','yyyy-mm-dd hh24:mi:ss') datevalue from dual; select to_char(sysdate,'yyyymmdd') from dual;
3、current_timestamp()以timestamp with time zone数据cdq回当前会放时区中的当前日期 timestamp_with_time_zone_value:=current_timestamp([timestamp_precision]) SQL> column sessiontimezone for a15 SQL> column current_timestamp format a36 SQL> select sessiontimezone,current_timestamp from dual;
4、dbtimezone()q回时区 varchar_value:=dbtimezone SQL> select dbtimezone from dual;
DBTIME ------ -07:00
SQL>
5、extract()扑և日期或间隔值的字段? date_value:=extract(date_field from [datetime_value|interval_value]) SQL> select extract(month from sysdate) "This Month" from dual;
This Month ---------- 11
SQL> select extract(year from add_months(sysdate,36)) "3 Years Out" from dual;
3 Years Out ----------- 2006
SQL>
6、last_day()q回包含了日期参数的月䆾的最后一天的日期 date_value:=last_day(date_value) SQL> select last_day(date'2000-02-01') "Leap Yr?" from dual;
Leap Yr? ---------- 29-2?-00
SQL> select last_day(sysdate) "Last day of this month" from dual;
Last day o ---------- 30-11?03
SQL>
7、localtimestamp()q回会话中的日期和时? timestamp_value:=localtimestamp SQL> column localtimestamp format a28 SQL> select localtimestamp from dual;
Y或YY或YYY q的最后一位,两位或三?Select to_char(sysdate,’YYY? from dualQ?002表示2002q? SYEAR或YEAR SYEAR使公元前的年份前加一负号 Select to_char(sysdate,’SYEAR? from dualQ?-1112表示公元?11 2q? Q 季度Q??月ؓW一季度 Select to_char(sysdate,’Q? from dualQ?2表示W二季度? MM 月䆾?Select to_char(sysdate,’MM? from dualQ?12表示12? RM 月䆾的罗马表C?Select to_char(sysdate,’RM? from dualQ?IV表示4? Month ?个字W长度表C的月䆾?Select to_char(sysdate,’Month? from dualQ?May后跟6个空DC?? WW 当年W几?Select to_char(sysdate,’WW? from dualQ?24表示2002q??3日ؓW?4? W 本月W几?Select to_char(sysdate,’W? from dualQ?2002q?0?日ؓW?? DDD 当年W几, 1?日ؓ001Q??日ؓ032 Select to_char(sysdate,’DDD? from dualQ?363 2002q? 2? 9日ؓW?63? DD 当月W几?Select to_char(sysdate,’DD? from dualQ?04 10?日ؓW?? D 周内W几?Select to_char(sysdate,’D? from dualQ?5 2002q??4日ؓ星期一 DY 周内W几天羃?Select to_char(sysdate,’DY? from dualQ?SUN 2002q??4日ؓ星期? HH或HH12 12q制时?Select to_char(sysdate,’HH? from dualQ?02 午夜2点过8分ؓ02 HH24 24时?Select to_char(sysdate,’HH24? from dualQ?14 下午2?8分ؓ14 MI 分钟?0?9) Select to_char(sysdate,’MI? from dualQ?17下午4?7? SS U数(0?9) Select to_char(sysdate,’SS? from dualQ?22 11??2U? 提示注意不要MM格式用于分钟(分钟应该使用MI)。MM是用于月份的格式Q将它用于分钟也能工作,但结果是错误的?
现在l出一些实践后的用法:
1。上月末天:
SQL> select to_char(add_months(last_day(sysdate),-1),'yyyy-MM-dd') LastDay from dual;
LASTDAY ---------- 2005-05-31
2。上月今?/p>
SQL> select to_char(add_months(sysdate,-1),'yyyy-MM-dd') PreToday from dual;
PRETODAY ---------- 2005-05-21
3.上月首天
SQL> select to_char(add_months(last_day(sysdate)+1,-2),'yyyy-MM-dd') firstDay from dual;
FIRSTDAY ---------- 2005-05-01
4.按照每周q行l计
SQL> select to_char(sysdate,'ww') from dual group by to_char(sysdate,'ww');
TO -- 25
5。按照每月进行统?/p>
SQL> select to_char(sysdate,'mm') from dual group by to_char(sysdate,'mm');
TO -- 06
6。按照每季度q行l计
SQL> select to_char(sysdate,'q') from dual group by to_char(sysdate,'q');
T - 2
7。按照每q进行统?/p>
SQL> select to_char(sysdate,'yyyy') from dual group by to_char(sysdate,'yyyy');
TO_C ---- 2005
到此l束?/p>
阅读全文(180) | 回复(1) | 引用通告(0) | ~辑
回复:Oracle的日期函? smilepig(游客)发表评论?005-7-25 13:36:49
SQL> select to_char(sysdate,'DAY','NLS_DATE_LANGUAGE=''SIMPLIFIED CHINESE''') fr om dual;
]]>SQLServer和Oracle常用函数Ҏhttp://www.aygfsteel.com/sunjavaee/archive/2006/08/02/61411.html每天q步一点点每天q步一点点Wed, 02 Aug 2006 14:46:00 GMThttp://www.aygfsteel.com/sunjavaee/archive/2006/08/02/61411.htmlhttp://www.aygfsteel.com/sunjavaee/comments/61411.htmlhttp://www.aygfsteel.com/sunjavaee/archive/2006/08/02/61411.html#Feedback0http://www.aygfsteel.com/sunjavaee/comments/commentRss/61411.htmlhttp://www.aygfsteel.com/sunjavaee/services/trackbacks/61411.htmlSQLServer和Oracle是大家经常用到的数据库,在此感谢作者ȝ些常用函C供大家参考?
数学函数Q?br /> 1.l对? S:select abs(-1) value O:select abs(-1) value from dual
2.取整(? S:select ceiling(-1.001) value O:select ceil(-1.001) value from dual
3.取整Q小Q? S:select floor(-1.001) value O:select floor(-1.001) value from dual
4.取整Q截取) S:select cast(-1.002 as int) value O:select trunc(-1.002) value from dual
5.四舍五入 S:select round(1.23456,4) value 1.23460 O:select round(1.23456,4) value from dual 1.2346
6.e为底的幂 S:select Exp(1) value 2.7182818284590451 O:select Exp(1) value from dual 2.71828182
7.取e为底的对? S:select log(2.7182818284590451) value 1 O:select ln(2.7182818284590451) value from dual; 1
8.?0为底Ҏ S:select log10(10) value 1 O:select log(10,10) value from dual; 1
9.取^? S:select SQUARE(4) value 16 O:select power(4,2) value from dual 16
10.取^Ҏ S:select SQRT(4) value 2 O:select SQRT(4) value from dual 2
11.求Q意数为底的幂 S:select power(3,4) value 81 O:select power(3,4) value from dual 81
12.取随机数 S:select rand() value O:select sys.dbms_random.value(0,1) value from dual;
13.取符? S:select sign(-8) value -1 O:select sign(-8) value from dual -1
14.圆周? S:SELECT PI() value 3.1415926535897931 O:不知?
15.sin,cos,tan 参数都以弧度为单? 例如Qselect sin(PI()/2) value 得到1QSQLServerQ?
16.Asin,Acos,Atan,Atan2 q回弧度
17.弧度角度互换(SQLServerQOracle不知? DEGREESQ弧?〉角? RADIANSQ角?〉弧? 数值间比较Q?br /> 18. 求集合最大? S:select max(value) value from (select 1 value union select -2 value union select 4 value union select 3 value)a
O:select greatest(1,-2,4,3) value from dual
19. 求集合最? S:select min(value) value from (select 1 value union select -2 value union select 4 value union select 3 value)a
O:select least(1,-2,4,3) value from dual
20.如何处理null?F2中的null?0代替) S:select F1,IsNull(F2,10) value from Tbl O:select F1,nvl(F2,10) value from Tbl
21.求字W序? S:select ascii('a') value O:select ascii('a') value from dual
22.从序h字符 S:select char(97) value O:select chr(97) value from dual
23.q接 S:select '11'+'22'+'33' value O:select CONCAT('11','22') 33 value from dual
23.子串位置 --q回3 S:select CHARINDEX('s','sdsq',2) value O:select INSTR('sdsq','s',2) value from dual
23.模糊子串的位|?--q回2,参数L中间%则返? S:select patindex('%d%q%','sdsfasdqe') value O:oracle没发玎ͼ但是instr可以通过W四个参数控制出现次? select INSTR('sdsfasdqe','sd',1,2) value from dual q回6
24.求子? S:select substring('abcd',2,2) value O:select substr('abcd',2,2) value from dual
25.子串代替 q回aijklmnef S:SELECT STUFF('abcdef', 2, 3, 'ijklmn') value O:SELECT Replace('abcdef', 'bcd', 'ijklmn') value from dual
26.子串全部替换 S:没发? O:select Translate('fasdbfasegas','fa','? ) value from dual
27.长度 S:len,datalength O:length
28.大小写{?lower,upper
29.单词首字母大? S:没发? O:select INITCAP('abcd dsaf df') value from dual
30.左补I格QLPAD的第一个参CؓI格则同space函数Q? S:select space(10)+'abcd' value O:select LPAD('abcd',14) value from dual
31.双I格QRPAD的第一个参CؓI格则同space函数Q? S:select 'abcd'+space(10) value O:select RPAD('abcd',14) value from dual
32.删除I格 S:ltrim,rtrim O:ltrim,rtrim,trim
33. 重复字符? S:select REPLICATE('abcd',2) value O:没发?
34.发音怼性比?q两个单词返回gP发音相同) S:SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe') O:SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe') from dual SQLServer中用SELECT DIFFERENCE('Smithers', 'Smythers') 比较soundex的差 q回0-4Q?为同韻I1最? 日期函数Q?br /> 35.pȝ旉 S:select getdate() value O:select sysdate value from dual
36.前后几日 直接与整数相加减
37.求日? S:select convert(char(10),getdate(),20) value O:select trunc(sysdate) value from dual select to_char(sysdate,'yyyy-mm-dd') value from dual
38.求时? S:select convert(char(8),getdate(),108) value O:select to_char(sysdate,'hh24:mm:ss') value from dual
参数---------------------------------下表需要补? year yy, yyyy quarter qq, q (季度) month mm, m (m O无效) dayofyear dy, y (O表星? day dd, d (d O无效) week wk, ww (wk O无效) weekday dw (O不清? Hour hh,hh12,hh24 (hh12,hh24 S无效) minute mi, n (n O无效) second ss, s (s O无效) millisecond ms (O无效) ----------------------------------------------
40.当月最后一? S:不知? O:select LAST_DAY(sysdate) value from dual
41.本星期的某一天(比如星期日) S:不知? O:SELECT Next_day(sysdate,7) vaule FROM DUAL;
42.字符串{旉 S:可以直接转或者select cast('2004-09-08'as datetime) value O:SELECT To_date('2004-01-05 22:09:38','yyyy-mm-dd hh24-mi-ss') vaule FROM DUAL;
43.求两日期某一部分的差Q比如秒Q? S:select datediff(ss,getdate(),getdate()+12.3) value O:直接用两个日期相减(比如d1-d2=12.3Q? SELECT (d1-d2)*24*60*60 vaule FROM DUAL;
44.Ҏ差值求新的日期Q比如分钟) S:select dateadd(mi,8,getdate()) value O:SELECT sysdate+8/60/24 vaule FROM DUAL;
45.求不同时区时? S:不知? O:SELECT New_time(sysdate,'ydt','gmt' ) vaule FROM DUAL;
/** This example shows how to establish a connection * and send messages to the JMS queue. The classes in this * package operate on the same JMS queue. Run the classes together to * witness messages being sent and received, and to browse the queue * for messages. The class is used to send messages to the queue. * * @author Copyright (c) 1999-2003 by BEA Systems, Inc. All Rights Reserved. */ public class QueueSend { // Defines the JNDI context factory. public final static String JNDI_FACTORY="weblogic.jndi.WLInitialContextFactory";
// Defines the JNDI provider url. public final static String PROVIDER_URL=" t3://localhost:80";
// Defines the JMS connection factory for the queue. public final static String JMS_FACTORY="SendJMSFactory";
// Defines the queue. public final static String QUEUE="SendJMSQueue";
/** * Creates all the necessary objects for sending * messages to a JMS queue. * * @param ctx JNDI initial context * @param queueName name of queue * @exception NamingException if operation cannot be performed * @exception JMSException if JMS fails to initialize due to internal error */ public void init(Context ctx, String queueName) throws NamingException, JMSException { qconFactory = (QueueConnectionFactory) ctx.lookup(JMS_FACTORY); qcon = qconFactory.createQueueConnection(); qsession = qcon.createQueueSession(false, Session.AUTO_ACKNOWLEDGE); queue = (Queue) ctx.lookup(queueName); qsender = qsession.createSender(queue); msg = qsession.createTextMessage(); qcon.start(); }
/** * Sends a message to a JMS queue. * * @param message message to be sent * @exception JMSException if JMS fails to send message due to internal error */ public void send(String message) throws JMSException { msg.setText(message); qsender.send(msg); }
/** * Closes JMS objects. * @exception JMSException if JMS fails to close objects due to internal error */ public void close() throws JMSException { qsender.close(); qsession.close(); qcon.close(); } /** main() method. * * @param args WebLogic Server URL * @exception Exception if operation fails */ public static void main(String[] args) throws Exception { InitialContext ic = getInitialContext(); QueueSend qs = new QueueSend(); qs.init(ic, QUEUE); readAndSend(qs); qs.close(); }
private static void readAndSend(QueueSend qs) throws IOException, JMSException { BufferedReader msgStream = new BufferedReader(new InputStreamReader(System.in)); String line=null; boolean quitNow = false; do { System.out.print("Enter message (\"quit\" to quit): "); line = msgStream.readLine(); if (line != null && line.trim().length() != 0) { qs.send(line); System.out.println("JMS Message Sent: "+line+"\n"); quitNow = line.equalsIgnoreCase("quit"); } } while (! quitNow);
}
private static InitialContext getInitialContext() throws NamingException { Hashtable env = new Hashtable(); env.put(Context.INITIAL_CONTEXT_FACTORY, JNDI_FACTORY); env.put(Context.PROVIDER_URL, PROVIDER_URL); return new InitialContext(env); }
/** * This example shows how to establish a connection to * and receive messages from a JMS queue. The classes in this * package operate on the same JMS queue. Run the classes together to * witness messages being sent and received, and to browse the queue * for messages. This class is used to receive and remove messages * from the queue. * * @author Copyright (c) 1999-2003 by BEA Systems, Inc. All Rights Reserved. */ public class QueueReceive implements MessageListener { // Defines the JNDI context factory. public final static String JNDI_FACTORY="weblogic.jndi.WLInitialContextFactory";
// Defines the JNDI provider url. public final static String PROVIDER_URL=" t3://localhost:80";
// Defines the JMS connection factory for the queue. public final static String JMS_FACTORY="SendJMSFactory";
// Defines the queue. public final static String QUEUE="SendJMSQueue";