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

SQLserverʱ¼ä¶Î²éѯ

eg:
select * from Exception_Log where DATEDIFF(day,OPER_DATE,getdate())<30 ;
/*
oper_date Ϊ±íÖвéѯ³öÀ´µÄʱ¼ä
getdate()ΪSQLServerÀïÃæ»ñµÃϵͳʱ¼äµÄº¯Êý
º¬Ò壺µ±Ç°Ê±¼ä-²éѯ³öÀ´µÄʱ¼äСÓÚ30Ìì
DateDiff¾ßÌåÓ÷¨£º
*/
DateDiff º¯Êý
·µ»ØÁ½¸öÈÕÆÚÖ®¼äµÄʱ¼ä¼ä¸ô¡£
DateDiff(interval, date1, date2 [,firstdayofweek[, firstweekofyear]])
DateDiff º¯ÊýµÄÓï·¨ÓÐÒÔϲÎÊý£º
²ÎÊý
interval
±ØÑ¡Ïî¡£String expression ±íʾÓÃÓÚ¼ÆËã date1 ºÍ date2 Ö®¼äµÄʱ¼ä¼ä¸ô¡£ÓйØÊýÖµ£¬Çë²ÎÔÄ“ÉèÖÔ²¿·Ö¡£
date1, date2
±ØÑ¡Ïî¡£ÈÕÆÚ±í´ïʽ¡£ÓÃÓÚ¼ÆËãµÄÁ½¸öÈÕÆÚ¡£
Firstdayofweek
¿ÉÑ¡Ïî¡£Ö¸¶¨ÐÇÆÚÖеÚÒ»ÌìµÄ³£Êý¡£Èç¹ûûÓÐÖ¸¶¨£¬ÔòĬÈÏΪÐÇÆÚÈÕ¡£ÓйØÊýÖµ£¬Çë²ÎÔÄ“ÉèÖÔ²¿·Ö¡£
Firstweekofyear
¿ÉÑ¡Ïî¡£Ö¸¶¨Ò»ÄêÖеÚÒ»Öܵij£Êý¡£Èç¹ûûÓÐÖ¸¶¨£¬ÔòĬÈÏΪ 1 Ô 1 ÈÕËùÔÚµÄÐÇÆÚ¡£ÓйØÊýÖµ£¬Çë²ÎÔÄ“ÉèÖÔ²¿·Ö¡£
ÉèÖÃ
interval ²ÎÊý¿ÉÒÔÓÐÒÔÏÂÖµ£º
ÉèÖà  ÃèÊö
yyyy  Äê
q  ¼¾¶È
m  ÔÂ
y  Ò»ÄêµÄÈÕÊý
d  ÈÕ
w  Ò»ÖܵÄÈÕÊý
ww ÖÜ
h  Ð¡Ê±
n  ·ÖÖÓ
s  Ãë
firstdayofweek ²ÎÊý¿ÉÒÔÓÐÒÔÏÂÖµ£º
³£Êý   Öµ  ÃèÊö
vbUseSystem 0  Ê¹ÓÃÇøÓòÓïÑÔÖ§³Ö (NLS) API ÉèÖá£
vbSunday  1  ÐÇÆÚÈÕ£¨Ä¬ÈÏ£©
vbMonday  2  ÐÇÆÚÒ»
vbTuesday  3  ÐÇÆÚ¶þ
vbWednesday 4  ÐÇÆÚÈý
vbThursday  5  ÐÇÆÚËÄ
vbFriday  6  ÐÇÆÚÎå
vbSaturday  7  ÐÇÆÚÁù
firstweekofyear ²ÎÊý¿ÉÒÔÓÐÒÔÏÂÖµ£º
³£Êý    Öµ  ÃèÊö
vbUseSystem  0  Ê¹ÓÃÇøÓòÓïÑÔÖ§³Ö (NLS) API ÉèÖá£
vbFirstJan1  1  ÓÉ 1 Ô 1 ÈÕËùÔÚµÄÐÇÆÚ¿ªÊ¼£¨Ä¬ÈÏ£©¡£
vbFirstFourDays  2  ÓÉÔÚÐÂÄêÖÐÖÁÉÙÓÐËÄÌìµÄµÚÒ»ÖÜ¿ªÊ¼¡£
vbFirstFullWeek  3  ÓÉÔÚеÄÒ»ÄêÖеÚÒ»¸öÍêÕûµÄÖÜ¿ªÊ¼¡£
˵Ã÷
DateDiff º¯ÊýÓÃÓÚÅжÏÔÚÁ½¸öÈÕÆÚÖ®¼ä´æÔÚµÄÖ¸¶¨Ê±¼ä¼ä¸ôµÄÊýÄ¿¡£ÀýÈç¿ÉÒÔʹÓà DateDiff ¼ÆËãÁ½¸öÈÕÆÚÏà²îµÄÌìÊý£¬»òÕßµ±Ììµ½µ±Äê×îºóÒ»ÌìÖ®¼äµÄÐÇÆÚÊý¡£
Òª¼ÆËã date1 ºÍ date2 Ïà²îµÄÌìÊý£¬¿ÉÒÔʹÓÓһÄêµÄÈÕÊý”£¨“y”£©»ò“ÈÕ”£¨“d”£©¡£µ± inter


Ïà¹ØÎĵµ£º

sqlserver FOR XML PATH Óï¾äµÄÓ¦ÓÃ

´úÂëÈçÏÂ:

DECLARE
@TempTable table(UserID int , UserName nvarchar(50));
insert into
@TempTable (UserID,UserName) values (1,'a')
insert into @TempTable
(UserID,UserName) values (2,'b')

select UserID,UserName
from @TempTable FOR XML PATH
ÔËÐÐÕâ¶Î½Å±¾£¬½«Éú³ÉÈçϽá¹û£º
¸´ÖÆ´úÂë ......

È«ÎÄË÷ÒýÔ­Àí¼°ÆäʾÀý£¨sqlserver£©

È«ÎļìË÷ÊǶԴóÊý¾ÝÎı¾½øÐÐË÷Òý£¬ÔÚ½¨Á¢µÄË÷ÒýÖжÔÒª²éÕҵĵ¥´Ê½øÐнøÐÐËÑË÷£¬¶¨Î»ÄÄЩÎı¾Êý¾Ý°üÀ¨ÒªËÑË÷µÄµ¥´Ê¡£Òò´Ë£¬È«ÎļìË÷µÄÈ«²¿¹¤×÷¾ÍÊǽ¨Á¢Ë÷ÒýºÍÔÚË÷ÒýÖÐËÑË÷¶¨Î»£¬ËùÓеŤ×÷¶¼ÊÇΧÈÆÕâÁ½¸öÀ´½øÐеġ£ÏÂÃæ¾ÍÖð¸ö½éÉÜ¡£
¡¡¡¡½¨Á¢È«ÎÄË÷ÒýÖÐÓÐÁ½Ïî·Ç³£ÖØÒª£¬Ò»¸öÊÇÈçºÎ¶ÔÎı¾½øÐзִʣ¬Ò»Êǽ¨Á¢Ë÷ÒýµÄÊý¾Ý½á¹¹¡ ......

SQLserverÖÐÓÃconvertº¯Êýת»»ÈÕÆÚ¸ñʽ

SQLserverÖÐÓÃconvertº¯Êýת»»ÈÕÆÚ¸ñʽ
2008-01-23 15:47
SQLserverÖÐÓÃconvertº¯Êýת»»ÈÕÆÚ¸ñʽ2008-01-15 15:51SQLserverÖÐÓÃconvertº¯Êýת»»ÈÕÆÚ¸ñʽ
SQL ServerÖÐÎÄ°æµÄĬÈϵÄÈÕÆÚ×Ö¶Îdatetime¸ñʽÊÇyyyy-mm-dd Thh:mm:ss.mmm
ÀýÈç:
select getdate()
2004-09-12 11:06:08.177
ÕûÀíÁËÒ»ÏÂSQL ServerÀïÃæ¿ÉÄÜ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ