SQL Serverȡϵͳµ±Ç°Ê±¼ä
getdate //»ñµÃϵͳµ±Ç°ÈÕÆÚ
datepart //»ñÈ¡ÈÕÆÚÖ¸¶¨²¿·Ö£¨ÄêÔÂÈÕʱ·Ö±í£©
getdate()º¯Êý£ºÈ¡µÃϵͳµ±Ç°µÄÈÕÆÚºÍʱ¼ä¡£·µ»ØֵΪdatetimeÀàÐ͵ġ£
Ó÷¨£ºgetdate()
Àý×Ó£º
select getdate() as dte,dateadd(day,-1,getdate()) as nowdat
Êä³ö½á¹û£º
dte nowdat
--------------------------- ---------------------------
1999-11-21 19:13:10.083 1999-11-20 19:13:10.083
(1 row(s) affected)
datepart()º¯Êý£ºÒÔÕûÊýµÄÐÎʽ·µ»Øʱ¼äµÄÖ¸¶¨²¿·Ö¡£
Ó÷¨£ºdatepart(datepart,date)
²ÎÊý˵Ã÷£ºdatepartʱҪ·µ»ØµÄʱ¼äµÄ²¿·Ö£¬³£ÓÃÈ¡Öµyear¡¢month¡¢day¡¢hour¡¢minute¡£
dateÊÇËùÖ¸¶¨µÄʱ¼ä¡£
Àý×Ó£º
SELECT DATEPART(month, GETDATE()) AS 'Month Number'
Êä³ö½á¹û£º
Month Number
------------
11
(1 row(s) affected)
dateadd()º¯Êý£ºÍ¨¹ý¸øÖ¸¶¨µÄʱ¼äµÄÖ¸¶¨²¿·Ö¼ÓÉÏÒ»¸öÕûÊýÖµÒÔ·µ»ØÒ»¸öÐÂʱ¼äÖµ¡£
Ó÷¨£ºdateadd(datepart,number,date)
²ÎÊý˵Ã÷£ºdatepart(ͬÉÏ)
date(ͬÉÏ)
numberÒªÔö¼ÓµÄÖµ£¬ÕûÐÍ£¬¿ÉÕý¿É¸º£¬ÕýÖµ·µ»ØdateÖ®ºóµÄʱ¼äÖµ£¬¸ºÖµ·µ»Ødate
֮ǰµÄʱ¼äÖµ
Àý×Ó£º
select getdate() as today
select dateadd(day,-1,getdate())
select dateadd(day,1,getdate())
Êä³ö£º
today
---------------------------
1999-11-21 19:42:41.410
(1 row(s) affected)
yesterday
---------------------------
1999-11-20 19:42:41.410
(1 row(s) affected)
tomorrow
---------------------------
1999-11-22 19:42:41.410
(1 row(s) affected)
datediff()º¯Êý£º·µ»ØÁ½¸öʱ¼äÒÔÖ¸¶¨Ê±¼ä²¿·ÖÀ´¼ÆËãµÄ²îÖµ¡£·µ»ØÕûÊýÖµ¡£Èç1991-6-12ºÍ1991-6-21Ö®¼äÒÔÌì
À´ËãÏà²î9Ìì,1998-6-12ºÍ1999-6-23°´ÄêËãÏà²î1Ä꣬1999-12-1ºÍ1999-3-12°´ÔÂËãÏà²î9¸öÔÂ
Ó÷¨£ºdatediff(darepart,date1,date2)
²ÎÊý˵Ã÷£ºdatepart£¨Í¬ÉÏ£©
date1¡¢date2(ͬÉÏdate)
Àý×Ó£º
select datediff(month,'1991-6-12','1992-6-21') as a
Êä³ö£º
a
-----------
12
(1 row(s) affected)
SQL server2005³£Óõĺ¯ÊýºÍ·ÖÒ³µÄ¼¸¸ö½â¾ö·½°¸£º
ʹÓÃʱ¼äºÍÈÕÆڵĺ¯Êý
getdate():»ñȡϵͳµ±Ç°Ê±¼ä
dateadd(datepart,number,date):¼ÆËãÔÚÒ»¸öʱ¼äµÄ»ù´¡ÉÏÔö¼ÓÒ»¸öʱ¼äºóµÄÐÂʱ¼äÖµ,±ÈÈ磺dateadd(yy,30,getdate())
datediff(datepart,startdate,enddate):¼ÆËãÁ½¸öʱ¼äµÄ²îÖµ,±ÈÈ磺datediff(yy,getdate(),'2008-08-08')
d
Ïà¹ØÎĵµ£º
µÚÒ»ÖÖ·½·¨: ʹÓÃoutput²ÎÊý
USE AdventureWorks;
GO
IF OBJECT_ID ( 'Production.usp_GetList', 'P' ) IS NOT NULL
DROP PROCEDURE Production.usp_GetList;
GO
CREATE PROCEDURE Production.usp_GetList @product varchar(40)
, @maxprice money
, ......
1¡¢Ã»ÓÐË÷Òý»òÕßûÓÐÓõ½Ë÷Òý(ÕâÊDzéѯÂý×î³£¼ûµÄÎÊÌ⣬ÊdzÌÐòÉè¼ÆµÄȱÏÝ)¡¡¡¡
¡¡¡¡2¡¢I/OÍÌÍÂÁ¿Ð¡£¬ÐγÉÁËÆ¿¾±Ð§Ó¦¡£¡¡¡¡
¡¡¡¡3¡¢Ã»Óд´½¨¼ÆËãÁе¼Ö²éѯ²»ÓÅ»¯¡£¡¡¡¡
¡¡¡¡4¡¢ÄÚ´æ²»×ã¡¡¡¡
¡¡¡¡5¡¢ÍøÂçËÙ¶ÈÂý¡¡¡¡
¡¡¡¡6¡¢²éѯ³öµÄÊý¾ÝÁ¿¹ý´ó£¨¿ÉÒÔ²ÉÓöà´Î²éѯ£¬ÆäËûµÄ·½·¨½µµÍÊý¾ÝÁ¿£©¡¡¡¡
¡¡¡¡7¡¢Ëø»òÕßËÀËø ......
1. ˵Ã÷£º¸´ÖƱí(Ö»¸´Öƽṹ£¬Ô´±íÃû£ºa£¬Ð±íÃû£ºb)
SQL: select * into b from a where 1<>1;
2. ˵Ã÷£º¿½±´±í(¿½±´Êý¾Ý£¬Ô´±íÃû£ºa£¬Ä¿±ê±íÃû£ºb)
SQL: insert into b(a, b, c) select d, e, f from b;
3. ......
1.°´ÐÕÊϱʻÅÅÐò:
Select * from TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as
2.·ÖÒ³SQLÓï¾ä
select * from(select (row_number() OVER (ORDER BY tab.ID Desc)) as rownum,tab.* from ±íÃû As tab) As t where rownum between ÆðʼλÖà And ½áÊøλÖÃ
3.»ñÈ¡µ±Ç°Êý¾Ý¿âÖеÄËùÓÐÓû§± ......