sql server 2000 ÈÕÆÚº¯Êý£¨Áª»ú°ïÖú£©
1.DATEADDº¯Êý
ÔÚÏòÖ¸¶¨ÈÕÆÚ¼ÓÉÏÒ»¶Îʱ¼äµÄ»ù´¡ÉÏ£¬·µ»ØÐ嵀 datetime Öµ¡£
Óï·¨
DATEADD ( datepart , number, date )
²ÎÊý
datepart
ÈÕÆÚ²¿·Ö
Ëõд
Year
yy, yyyy
quarter
qq, q
Month
mm, m
dayofyear
dy, y
Day
dd, d
Week
wk, ww
Hour
hh
minute
mi, n
second
ss, s
millisecond
ms
2. DATEDIFFº¯Êý
·µ»Ø¿çÁ½¸öÖ¸¶¨ÈÕÆÚµÄÈÕÆÚºÍʱ¼ä±ß½çÊý
²ÎÊý
datepart
Êǹ涨ÁËÓ¦ÔÚÈÕÆÚµÄÄÄÒ»²¿·Ö¼ÆËã²î¶îµÄ²ÎÊý¡£
ͬÉÏ
²âÊÔÊý¾Ý£º
if object_id('mma') is not null drop table mma
go
create table mma(mydate datetime)
go
insert into mma
select '20090203' union all
select '20090205' union all
select '20090209' union all
select '20091231' union all
select '20100102' union all
select '20100112' union all
select '20100116'
--²éѯ·µ»Ø±¾ÔµļǼ
·½·¨Ò»
select mydate from mma where datediff(m,mydate,getdate())=0(×¢µ±Ç°ÈÕÆÚÊÇ20100116)
·½·¨¶þ
SELECT * from mma WHERE CONVERT(CHAR(6), mydate,112)=CONVERT(CHAR(6),GETDATE(),112)
·½·¨Èý
select mydate from aa where substring(convert(varchar(20),getdate(),120),0,8)=substring(convert(varchar(20),mydate,120),0,8)
Ïà¹ØÎĵµ£º
²éѯ¼°É¾³ýÖظ´¼Ç¼µÄSQLÓï¾ä
1¡¢²éÕÒ±íÖжàÓàµÄÖظ´¼Ç¼£¬Öظ´¼Ç¼ÊǸù¾Ýµ¥¸ö×ֶΣ¨peopleId£©À´ÅжÏ
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
2¡¢É¾³ý±íÖжàÓàµÄÖظ´¼Ç¼£¬Öظ´¼Ç¼ÊÇ ......
µÚ1ÖÖ·½·¨£º
ÆóÒµ¹ÜÀíÆ÷
--¹ÜÀí
--ÓÒ¼üÊý¾Ý¿âά»¤¼Æ»®
--н¨Î¬»¤¼Æ»®
--<ÏÂÒ»²½>
--Ñ¡ÔñÄãÒª±¸·ÝµÄÊý¾Ý¿â
--<ÏÂÒ»²½>Ö±µ½"Ö¸¶¨Êý¾Ý¿â±¸·Ý¼Æ»®"ÕâÏî
--Ö¸¶¨´æ´¢±¸·ÝÎļþµÄλÖÃ,ÕâÀï¸ù¾ÝÐèҪѡÔñ±¸·Öµ½´Å´ø»¹ÊÇ´ÅÅÌ
--µ¥»÷µ÷¶ÈºóÃæµÄ"¸ü¸Ä"°´ ......
ÔÚÈëÃÅƪ£¬ÎÒÃÇѧ»áÁˣӣѣÌ×¢ÈëµÄÅжϷ½·¨£¬µ«ÕæÕýÒªÄõ½ÍøÕ¾µÄ±£ÃÜÄÚÈÝ£¬ÊÇÔ¶Ô¶²»¹»µÄ¡£½ÓÏÂÀ´£¬ÎÒÃǾͼÌÐøѧϰÈçºÎ´ÓÊý¾Ý¿âÖлñÈ¡ÏëÒª»ñµÃµÄÄÚÈÝ£¬Ê×ÏÈ£¬ÎÒÃÇÏÈ¿´¿´£Ó£Ñ£Ì×¢ÈëµÄÒ»°ã²½Ö裺
µÚÒ»½Ú¡¢£Ó£Ñ£Ì×¢ÈëµÄÒ»°ã²½Öè
¡¡¡¡Ê×ÏÈ£¬Åжϻ·¾³£¬Ñ°ÕÒ×¢Èëµã£¬ÅжÏÊý¾Ý¿âÀàÐÍ£¬ÕâÔÚÈëÃÅƪÒѾ½²¹ýÁË¡£
¡¡¡¡Æä´Î£¬¸ù¾Ý×¢Èë ......
Ò»£ºSQL Loader µÄÌصã
oracle×Ô¼º´øÁ˺ܶàµÄ¹¤¾ß¿ÉÒÔÓÃÀ´½øÐÐÊý¾ÝµÄǨÒÆ¡¢±¸·ÝºÍ»Ö¸´µÈ¹¤×÷¡£µ«ÊÇÿ¸ö¹¤¾ß¶¼ÓÐ×Ô¼ºµÄÌص㡣
±ÈÈç˵expºÍimp¿ÉÒÔ¶ÔÊý¾Ý¿âÖеÄÊý¾Ý½øÐе¼³öºÍµ¼³öµÄ¹¤×÷£¬ÊÇÒ»ÖֺܺõÄÊý¾Ý¿â±¸·ÝºÍ»Ö¸´µÄ¹¤¾ß£¬Òò´ËÖ÷ÒªÓÃÔÚÊý¾Ý¿âµÄÈȱ¸·ÝºÍ»Ö¸´·½Ãæ¡£ÓÐ×ÅËٶȿ죬ʹÓüòµ¥£¬¿ì½ÝµÄÓŵ㣻ͬʱҲÓÐһЩȱµ ......
±àд¸ßÐÔÄܵÄSQLÓï¾ä×¢ÒâÊÂÏî
ÔÚÓ¦ÓÃϵͳ¿ª·¢³õÆÚ£¬ÓÉÓÚ¿ª·¢Êý¾Ý¿âÊý¾Ý±È½ÏÉÙ£¬¶ÔÓÚ²éѯSQLÓï¾ä£¬¸´ÔÓÊÓͼµÄµÄ±àдµÈÌå»á²»³öSQLÓï¾ä¸÷ÖÖд·¨µÄÐÔÄÜÓÅÁÓ£¬µ«ÊÇÈç¹û½«Ó¦ÓÃϵͳÌύʵ¼ÊÓ¦Óúó£¬Ëæ×ÅÊý¾Ý¿âÖÐÊý¾ÝµÄÔö¼Ó£¬ÏµÍ³µÄÏìÓ¦ËٶȾͳÉΪĿǰϵͳÐèÒª½â¾öµÄ×îÖ÷ÒªµÄÎÊÌâÖ®Ò»¡£ÏµÍ³ÓÅ»¯ÖÐÒ»¸öºÜÖØÒªµÄ·½Ãæ¾ÍÊÇSQLÓï¾äµÄÓÅ ......