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
Ïà¹ØÎĵµ£º
´úÂëÈçÏÂ:
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
ÔËÐÐÕâ¶Î½Å±¾£¬½«Éú³ÉÈçϽá¹û£º
¸´ÖÆ´úÂë
......
·ÖÒ³´æ´¢¹ý³Ì
sp_QueryPaging
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:
-- Create date:
-- Description: ·ÖÒ³²éѯ
-- ·µ»ØµÄÊý¾ÝÖÐ×ÜÊÇÔÚÿÌõ¼Ç¼ǰ¼ÓÉÏÐкÅRowNumberÁС£
-- Èç¹ûδָ¶¨@StrSelect²ÎÊý£¬Ï൱ÓÚÉèÖÃËüΪ'*'
-- Èç¹ûδ ......
ÏÂÎçÐèÒªÅäÖñ¾»ú°²×°µÄmssqlÊý¾Ý¿âÄܹ»ÔÚÔ¶³Ì·ÃÎÊ£¬ÔÚÍøÉÏËÑË÷µÄ·½·¨£ºhttp://weste.net/2009/5-20/21244518910.html
°´ÕÕÕâ¸ö²½ÖèÒ»²½Ò»²½ÅäÖÃÏÂȥȴ·¢ÏÖÈÔÈ»²»ÄÜ·ÃÎÊ¡£Èç¹ûÅäÖÃprotocolµÄʱºòlisten all ÉèÖÃΪno£¬ÖØÆômssql·þÎñϵͳ»áÌáʾmssql·þÎñ×Ô¶¯Í£Ö¹£»Èç¹ûÉèÖÃΪyes¿ÉÒÔÆô¶¯È´ÔÚnetstatÁбíÖÐÎÞ·¨¼ì²éµ½¶Ô1433¶ ......
select a.UserName Óû§Ãû,b.LoginName ºÅÂë,c.MenuTitle ±êÌâ,max(a.AccessTime)·ÃÎÊʱ¼ä from JfAccessLog a,OaUser b,JfMenu c where CONVERT(char,a.AccessTime,112)=CONVERT(char,GETDATE(),112) and a.UserId=b.UserId and a.MenuId=c.MenuId and a.MenuId=@menu group by a.UserName,b.LoginName,c.Menu ......
ÔÚÆ½Ê±µÄ¹¤×÷¹ý³ÌÖУ¬×÷ΪDBA½ÇÉ«¹ÜÀíÊý¾Ý¿â£¬Í·ÄÔÖеÄÓ¡ÏóÍùÍùÊÇÊý¾Ý¿âʵÀýÃû³Æ£¬¶ø²»»áÈ¥¹ØÐÄServerµÄIP£¬¶ø×÷ΪDeveloperµÄ½ÇÉ«£¬ËûÃÇÍùÍùÏëÖªµÀÖªµÀServer IpºÍ¶Ë¿ÚºÅ¡£ËùÒÔ£¬DBA»á¾³£±»Îʼ°µ½£ºXXXʵÀýµÄIPºÍ¶Ë¿ÚºÅÊÇʲô£¿
Õâ¸öÎÊÌ⣬µ±È»ÎÒÃÇ¿ÉÒÔLoginµ½OS²é¿´IP¡¢Ê¹ÓÃÅäÖÆ¹ÜÀí¹¤¾ß»ñÈ¡µ½¶Ë¿ÚºÅ¡£µ«ÊÇ£¬Õâ¸ö·½·¨·Ç ......