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
Ïà¹ØÎĵµ£º
USE master
GO
DECLARE @SQL VARCHAR(MAX);
SET @SQL=''
SELECT @SQL=@SQL+'; KILL '+RTRIM(SPID)
from master..sysprocesses
WHERE dbid=DB_ID('hotel');
EXEC(@SQL);
GO
ALTER DATABASE hotel SET MULTI_USER ......
ÊìϤSQL SERVER 2000µÄÊý¾Ý¿â¹ÜÀíÔ±¶¼ÖªµÀ£¬ÆäDTS¿ÉÒÔ½øÐÐÊý¾ÝµÄµ¼Èëµ¼³ö£¬Æäʵ£¬ÎÒÃÇÒ²¿ÉÒÔʹÓÃTransact-SQLÓï¾ä½øÐе¼Èëµ¼³ö²Ù×÷¡£ÔÚTransact-SQLÓï¾äÖУ¬ÎÒÃÇÖ÷ҪʹÓÃOpenDataSourceº¯Êý¡¢OPENROWSET º¯Êý£¬¹ØÓÚº¯ÊýµÄÏêϸ˵Ã÷£¬Çë²Î¿¼SQLÁª»ú°ïÖú¡£ÀûÓÃÏÂÊö·½·¨£¬¿ÉÒÔÊ®·ÖÈÝÒ×µØʵÏÖSQL SERVER¡¢ACCESS¡¢EXCELÊý¾ ......
ServerÊý¾Ý¿âÖÐʵÏÖ×îÓÅ×î¼òµÄÕû¸ö¹ý³Ì¡£
Ëã·¨ÊǼÆËã»ú¿ÆѧÖÐÒ»¸öÖØÒªµÄÑо¿·½Ïò£¬Êǽâ¾ö¸´ÔÓÎÊÌâµÄ¹Ø¼ü¡£ÔÚ¼ÆËã»úÊÀ½çÖУ¬Ëã·¨ÎÞ´¦²»ÔÚ¡£Êý¾Ý¿âÊÇ´æ´¢Êý¾ÝºÍÖ´ÐдóÅúÁ¿¼ÆËãµÄ³¡Ëù£¬ÔÚÊý¾Ý¿âÖÐʹÓÃһЩ¼òµ¥µÄSQLÃüÁ½øÐд洢¡¢²éѯ¡¢Í³¼Æ¡¢ÒÔ½â¾öÏÖʵÊÀ½çÖеÄÎÊÌâÒѾÊÇÂżû²»ÏÊ¡£Ëæ×ÅÊý¾ÝÁ¿µÄ´ó·ù¶ÈÔö¼ÓºÍÒµÎñ¹æ ......
µÚÒ»ÖÖ·½·¨: ʹÓÃ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 Âß¼Êý¾Ý¿âºÍ±íµÄÉè¼Æ
¡¡¡¡Êý¾Ý¿âµÄÂß¼Éè¼Æ¡¢°üÀ¨±íÓë±íÖ®¼äµÄ¹ØϵÊÇÓÅ»¯¹ØϵÐÍÊý¾Ý¿âÐÔÄܵĺËÐÄ¡£Ò»¸öºÃµÄÂß¼Êý¾Ý¿âÉè¼Æ¿ÉÒÔΪÓÅ»¯Êý¾Ý¿âºÍÓ¦ÓóÌÐò´òÏÂÁ¼ºÃµÄ»ù´¡¡£
¡¡¡¡±ê×¼»¯µÄÊý¾Ý¿âÂß¼Éè¼Æ°üÀ¨ÓöàµÄ¡¢ÓÐÏ໥¹ØϵµÄÕ±íÀ´´úÌæºÜ¶àÁеij¤Êý¾Ý±í¡£ÏÂÃæÊÇһЩʹÓñê×¼»¯±íµÄһЩºÃ´¦¡£
A:ÓÉÓÚ±íÕ£¬Òò´Ë¿É ......