Ò׽ؽØͼÈí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

SQLÖÐDATEADDºÍDATEDIFFµÄÓ÷¨

¹Ì¶¨ÈÕÆÚ¼õÒ»¸öÔ£¨ÈÕ¡¢Ä꣩
dateadd(±í´ïʽ1£¬±í´ïʽ2£¬±í´ïʽ3£©
±í´ïʽ1ΪMM»òYY»òDD
±í´ïʽ2Ϊ1
±í´ïʽ3ΪҪ¼õµÄÈÕÆÚ Ò»¸öÔµĵÚÒ»Ìì µÚÒ»¸öÀý×Ó£¬ÎÒ½«¸æËßÄãÈçºÎ´Óµ±Ç°ÈÕÆÚÈ¥Õâ¸öÔµÄ×îºóÒ»Ìì¡£Çë×¢Ò⣺Õâ¸öÀý×ÓÒÔ¼°ÕâƪÎÄÕÂÖеÄÆäËûÀý×Ó¶¼½«Ö»Ê¹ÓÃDATEDIFFºÍDATEADDº¯ÊýÀ´¼ÆËãÎÒÃÇÏëÒªµÄÈÕÆÚ¡£Ã¿Ò»¸öÀý×Ó¶¼½«Í¨¹ý¼ÆË㵫ǰµÄʱ¼ä¼ä¸ô£¬È»ºó½øÐмӼõÀ´µÃµ½ÏëÒª¼ÆËãµÄÈÕÆÚ¡£ ÕâÊǼÆËãÒ»¸öÔµÚÒ»ÌìµÄSQL ½Å±¾£º Select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) ÎÒÃÇ°ÑÕâ¸öÓï¾ä·Ö¿ªÀ´¿´¿´ËüÊÇÈçºÎ¹¤×÷µÄ¡£×îºËÐĵĺ¯ÊýÊÇgetdate()£¬´ó²¿·ÖÈ˶¼ÖªµÀÕâ¸öÊÇ·µ»Øµ±Ç°µÄÈÕÆÚºÍʱ¼äµÄº¯Êý¡£ÏÂÒ»¸öÖ´Ðеĺ¯ÊýDATEDIFF(mm,0,getdate())ÊǼÆË㵱ǰÈÕÆں͡°1900-01-01 00:00:00.000¡±Õâ¸öÈÕÆÚÖ®¼äµÄÔÂÊý¡£¼Çס£ºÊ±ÆÚºÍʱ¼ä±äÁ¿ºÍºÁÃëÒ»ÑùÊÇ´Ó¡°1900-01-01 00:00:00.000¡±¿ªÊ¼¼ÆËãµÄ¡£Õâ¾ÍÊÇΪʲôÄã¿ÉÒÔÔÚDATEDIFFº¯ÊýÖÐÖ¸¶¨µÚÒ»¸öʱ¼ä±í´ïʽΪ¡°0¡±¡£ÏÂÒ»¸öº¯ÊýÊÇDATEADD£¬Ôö¼Óµ±Ç°ÈÕÆÚµ½¡°1900-01-01¡±µÄÔÂÊý¡£Í¨¹ýÔö¼ÓÔ¤¶¨ÒåµÄÈÕÆÚ¡°1900-01-01¡±ºÍµ±Ç°ÈÕÆÚµÄÔÂÊý£¬ÎÒÃÇ¿ÉÒÔ»ñµÃÕâ¸öÔµĵÚÒ»Ìì¡£ÁíÍ⣬¼ÆËã³öÀ´µÄÈÕÆÚµÄʱ¼ä²¿·Ö½«»áÊÇ¡°00:00:00.000¡±¡£ Õâ¸ö¼ÆËãµÄ¼¼ÇÉÊÇÏȼÆË㵱ǰÈÕÆÚµ½¡°1900-01-01¡±µÄʱ¼ä¼ä¸ôÊý£¬È»ºó°ÑËü¼Óµ½¡°1900-01-01¡±ÉÏÀ´»ñµÃÌØÊâµÄÈÕÆÚ£¬Õâ¸ö¼¼ÇÉ¿ÉÒÔÓÃÀ´¼ÆËãºÜ¶à²»Í¬µÄÈÕÆÚ¡£ÏÂÒ»¸öÀý×ÓÒ²ÊÇÓÃÕâ¸ö¼¼ÇÉ´Óµ±Ç°ÈÕÆÚÀ´²úÉú²»Í¬µÄÈÕÆÚ¡£ ±¾ÖܵÄÐÇÆÚÒ» ÕâÀïÎÒÊÇÓÃÖÜ(wk)µÄʱ¼ä¼ä¸ôÀ´¼ÆËãÄÄÒ»ÌìÊDZ¾ÖܵÄÐÇÆÚÒ»¡£ Select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) Ò»ÄêµÄµÚÒ»Ìì ÏÖÔÚÓÃÄê(yy)µÄʱ¼ä¼ä¸ôÀ´ÏÔʾÕâÒ»ÄêµÄµÚÒ»Ìì¡£ Select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) ¼¾¶ÈµÄµÚÒ»Ìì ¼ÙÈçÄãÒª¼ÆËãÕâ¸ö¼¾¶ÈµÄµÚÒ»Ì죬Õâ¸öÀý×Ó¸æËßÄã¸ÃÈçºÎ×ö¡£ Select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) µ±ÌìµÄ°ëÒ¹ Ôø¾­ÐèҪͨ¹ýgetdate()º¯ÊýΪÁË·µ»Øʱ¼äÖµ½Øµôʱ¼ä²¿·Ö£¬¾Í»á¿¼Âǵ½µ±Ç°ÈÕÆÚÊDz»ÊÇÔÚ°ëÒ¹¡£¼ÙÈçÕâÑù£¬Õâ¸öÀý×ÓʹÓÃDATEDIFFºÍDATEADDº¯ÊýÀ´»ñµÃ°ëÒ¹µÄʱ¼äµã¡£ Select DATEADD(dd, DATEDIFF(dd,0,getdate()), 0) ÉîÈëDATEDIFFºÍDATEADDº¯Êý¼ÆËã Äã¿ÉÒÔÃ÷°×£¬Í¨¹ýʹÓüòµ¥µÄDATEDIFFºÍDATEADDº¯Êý¼ÆË㣬Äã¿ÉÒÔ·¢Ïֺܶ಻ͬµÄ¿ÉÄÜÓÐÒâÒåµÄÈÕÆÚ¡£ ĿǰΪֹµÄËùÓÐÀý×ÓÖ»Êǽö½ö¼ÆË㵱ǰµÄʱ¼äºÍ¡°1900-01-01¡±Ö®¼äµÄʱ¼ä¼ä¸ôÊýÁ¿£¬È»ºó°ÑËü¼Óµ½¡°1900-01-01¡±µÄʱ¼ä¼ä¸ôÉÏÀ´


Ïà¹ØÎĵµ£º

Oracle SQLµÄÓ²½âÎöºÍÈí½âÎö


 
˵µ½Èí½âÎö£¨soft prase£©ºÍÓ²½âÎö£¨hard prase£©£¬¾Í²»Äܲ»ËµÒ»ÏÂOracle¶ÔsqlµÄ´¦Àí¹ý³Ì¡£µ±Äã·¢³öÒ»ÌõsqlÓï¾ä½»¸¶Oracle£¬ÔÚÖ´ÐкͻñÈ¡½á¹ûÇ°£¬Oracle¶Ô´Ësql½«½øÐм¸¸ö²½ÖèµÄ´¦Àí¹ý³Ì£º
    1¡¢Óï·¨¼ì²é£¨syntax check£©
    ¼ì²é´ËsqlµÄƴдÊÇ·ñÓï ......

ÔÚ×öSQL¹ÊÕÏתÒÆȺ¼¯µÄʱºòÓ¦¸ÃÑ¡ÔñÄÄÖÖģʽ£¿

Èç¹ûÎÒÃǵÄSQL ServerÒª±£Ö¤¸ß¿ÉÓÃÐÔ£¬ÄÇô¿ÉÒÔ²ÉÓùÊÕÏתÒÆȺ¼¯¡£×î¼òµ¥µÄ¹ÊÕÏתÒÆȺ¼¯ÊÇÁ½Ì¨·þÎñÆ÷£¬Ò»Ì¨×ö»î¶¯µÄ·þÎñÆ÷£¬Áíһ̨×ö±¸Ó÷þÎñÆ÷£¬Õâ¾ÍÊÇAPģʽµÄCluster¡£ÁíÍâÒ»¸öģʽ¾ÍÊÇAAģʽ£¬Ò²¾ÍÊÇÁ½Ì¨·þÎñÆ÷¶¼ÊÇÔËÐÐSQL ServerʵÀý¡£
SQL Server²»ÏñOracleÒ»ÑùÓÐRAC£¬ËùÒÔ²»¿ÉÄÜ˵Á½Ì¨·þÎñÆ÷ͬʱÔËÐÐͬһ¸öʵÀý£¬Ïë ......

SQLº¯ÊýÖ®ËÄÉáÎåÈë

SQLº¯ÊýÖ®ËÄÉáÎåÈ루ת×Ôhttp://ln1058.javaeye.com/blog/191502£©
ÎÊÌâ1£º
SELECT CAST('123.456' as decimal) ½«»áµÃµ½ 123£¨Ð¡ÊýµãºóÃæµÄ½«»á±»Ê¡ÂÔµô£©¡£
Èç¹ûÏ£ÍûµÃµ½Ð¡ÊýµãºóÃæµÄÁ½Î»¡£
ÔòÐèÒª°ÑÉÏÃæµÄ¸ÄΪ
SELECT CAST('123.456' as decimal(38, 2)) ===>123.46
×Ô¶¯ËÄÉáÎåÈëÁË£ ......

Java Á¬½ÓÊý¾Ý¿â¡¢Ö´ÐÐSQL·â×°Àà

/*µ±Ö´ÐÐSQLʱ·¢ÏÖ²»ÄÜÁ¬½ÓÊý¾Ý¿â£¬ÏÈÖØÁ¬Ò»´Î*/
 
import java.io.FileInputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
......

ÕâÑùµÄsql£¬ÎÒÈ´×öµ½ÁË¡£

 ÕâÊÇÒ»¸öCSDN Oracle¿ª·¢°æ¿éÀÍøÓÑÎʹýµÄÒ»µÀÌâ
ÌâÈçÏÂ
create table A_TEST
(
PAYOUT_ITEM_CODE VARCHAR2(30) not
null,
FORMULA_DET VARCHAR2(1000)
)
create table B_TEST
(
ELEMENT_ID VARCHAR2(5) not null,
NAME VARCHAR2(41)
)  
Çóдoracle¶à¸ö×Ö·ûÌæ»»£¨ÓвâÊÔÊý¾Ý£©
inser ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ