SQLÖÐDATEADDºÍDATEDIFFµÄÓ÷¨(ÈÕÆÚ)
SQLÖÐDATEADDºÍDATEDIFFµÄÓ÷¨
2009-12-10 22:16
¹Ì¶¨ÈÕÆÚ¼õÒ»¸öÔ£¨ÈÕ¡¢Ä꣩
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º¯Êý¼ÆË㣬Äã¿ÉÒÔ·¢Ïֺܶ಻ͬµÄ¿ÉÄÜÓÐÒâÒåµÄÈÕÆÚ¡£
Ä¿Ç°
Ïà¹ØÎĵµ£º
ÀýÈç:
select getdate()
2004-09-12 11:06:08.177
Õâ¶ÔÓÚÔÚÒª²»Í¬Êý¾Ý¿â¼äתÒÆÊý¾Ý»òÕßÏ°¹ßoracleÈÕÆÚ¸ñʽYYYY-MM-DD HH24:MI:SSµÄÈ˶àÉÙÓÐЩ²»·½±ã.
ÎÒÕûÀíÁËÒ»ÏÂSQL ServerÀïÃæ¿ÉÄܾ³£»áÓõ½µÄÈÕÆÚ¸ñʽת»»·½·¨:
¾ÙÀýÈçÏÂ:
select CONVERT(varchar, getdate(), 120 )
2004-09-12 11:06:08
select replace(replac ......
Ò»¡¢»ù´¡
1¡¢ËµÃ÷£º´´½¨Êý¾Ý¿â
CREATE DATABASE database-name
2¡¢ËµÃ÷£ºÉ¾³ýÊý¾Ý¿â
drop database dbname
3¡¢ËµÃ÷£º±¸·Ýsql server
--- ´´½¨ ±¸·ÝÊý¾ÝµÄ device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
--- ¿ªÊ¼ ±¸·Ý
BACKUP DATABASE pubs TO testBack
4¡¢Ëµ ......
SQL²Ù×÷È«¼¯
ÏÂÁÐÓï¾ä²¿·ÖÊÇMssqlÓï¾ä£¬²»¿ÉÒÔÔÚaccessÖÐʹÓá£
SQL·ÖÀࣺ
DDL—Êý¾Ý¶¨ÒåÓïÑÔ(CREATE£¬ALTER£¬DROP£¬DECLARE)
DML—Êý¾Ý²Ù×ÝÓïÑÔ(SELECT£¬DELETE£¬UPDATE£¬INSERT)
DCL—Êý¾Ý¿ØÖÆÓïÑÔ(GRANT£¬REVOKE£¬COMMIT£¬ROLLBACK)
Ê×ÏÈ,¼òÒª½éÉÜ»ù´¡Óï¾ä£º
1¡¢ËµÃ ......
Ç°ÑÔ£º
sql_trace ÊÇÎÒÔÚ¹¤×÷Öо³£ÒªÓõ½µÄµ÷ÓŹ¤¾ß£¬Ïà±È½Ïstatspack ÎÒ¸üÔ¸ÒâÓÃÕâ¸ö¹¤¾ß¡£
ÒòΪÊý¾Ý¿âÂýÔÒòµÄ85%ÒÔÉÏÊÇÓÉÓÚsqlÎÊÌâÔì³ÉµÄ£¬statspackûÓÐsqlµÄÖ´Ðмƻ®¡£ÏÔʾûÓÐËüÖ±¹Û£¬·½±ã£¬¶ÔÏëÒªÕë¶ÔÐÔ²»Ç¿£¬
1£¬½éÉÜÊý¾Ý¿âµ÷ÓÅÐèÒª¾³£»áÓõ½µÄ¹¤¾ß ......