ÄúµÄλÖÃÊÇ£º ³£¼ûÎÊÌâ - ¼¼ÊõÎÄÕÂ

mssql2005ʱ¼ä²éѯ

ÈËÆø:2524 À´Ô´£ºÒÕ½¨Íø ·¢²¼ÓÚ£º2016-05-13 07:55:24

¡¡¡¡ºËÐÄÌáʾ£ºselect*frombl_datewhereout_datetimebetweenYEAR(getdate())+MONTH(getdate())+day(getdate()-1)+¡®07: ...

select * from bl_date 
where out_datetime
between YEAR(getdate())+MONTH(getdate())+day(getdate()-1)+¡®07:00:00'
and YEAR(getdate())+MONTH(getdate())+day(getdate())+¡®07:00:00'

select *
from tb
where datediff(dd,date,getdate()) <= 7

where datediff(day,[date],getdate())<7


DELETE FROM PzTable WHERE (PZTIME <GETDATE() - 120)


select datediff(day,'2014-01-01 00:00:00','2014-01-02 00:00:00')  --Ìì
select datediff(hour,'2014-01-01 00:00:00','2014-01-02 00:00:00')  --ʱ
select datediff(second,'2014-01-01 00:00:00','2014-01-02 00:00:00')  --·ÖÖÓ
select datediff(minute ,'2014-01-01 00:00:00','2014-01-02 00:00:00')  --Ãë


½ñÌìµÄËùÓÐÊý¾Ý£ºselect * from ±íÃû where DateDiff(dd,datetimeÀàÐÍ×Ö¶Î,getdate())=0

×òÌìµÄËùÓÐÊý¾Ý£ºselect * from ±íÃû where DateDiff(dd,datetimeÀàÐÍ×Ö¶Î,getdate())=1

7ÌìÄÚµÄËùÓÐÊý¾Ý£ºselect * from ±íÃû where DateDiff(dd,datetimeÀàÐÍ×Ö¶Î,getdate())<=7

30ÌìÄÚµÄËùÓÐÊý¾Ý£ºselect * from ±íÃû where DateDiff(dd,datetimeÀàÐÍ×Ö¶Î,getdate())<=30

±¾ÔµÄËùÓÐÊý¾Ý£ºselect * from ±íÃû where DateDiff(mm,datetimeÀàÐÍ×Ö¶Î,getdate())=0

±¾ÄêµÄËùÓÐÊý¾Ý£ºselect * from ±íÃû where DateDiff(yy,datetimeÀàÐÍ×Ö¶Î,getdate())=0


²éѯ½ñÌìÊǽñÄêµÄµÚ¼¸Ì죺 select datepart(dayofyear,getDate())

²éѯ½ñÌìÊDZ¾Ôµĵڼ¸Ì죺1. select datepart(dd, getDate())  
                                                2.select day(getDate())

²éѯ±¾ÖܵÄÐÇÆÚÒ»ÈÕÆÚÊǶàÉÙ £¨×¢Ò⣺ָ¶¨ÈÕÆÚ²»ÄÜÊÇÖÜÈÕ£¬Èç¹ûÊÇÖÜÈÕ»á¼ÆËãµ½ÏÂÖÜһȥ¡£ËùÒÔÈç¹û

ÊÇÖÜÈÕÒª¼õÒ»Ì죩
SELECT DATEADD(wk,DATEDIFF(wk,0,getdate()),0)

²éѯ×òÌìÈÕÆÚ£ºselect convert(char,dateadd(DD,-1,getdate()),111)  //111ÊÇÑùʽºÅ£¬£¨100-114£©


²éѯ±¾ÔµÚÒ»ÌìÈÕÆÚ£ºSelect DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) as firstday

²éѯ±¾ÔÂ×îºóÒ»ÌìÈÕÆÚ£ºSelect dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)) as 

lastday      //ÐÞ¸Ä-3µÄÖµ»áÓÐÏàÓ¦µÄ±ä»¯

±¾ÔÂÓжàÉÙÌ죺select datepart(dd,dateadd(dd,-1,dateadd(mm,1,cast((cast(year(getdate()) as 

varchar)+'-'+cast(month(getdate()) as varchar)+'-01' ) as datetime ))))

ÇóÁ½¸öʱ¼ä¶ÎÏà²î¼¸Ì죺select datediff(day,'2012/8/1','2012/8/20') as daysum
ÔÚÖ¸¶¨µÄÈÕÆÚÉÏ¡ÀNÌ죺select convert(char,dateadd(dd,1,'2012/8/20'),111) as riqi    //Êä³ö

2012/8/21

ÔÚÖ¸¶¨µÄÈÕÆÚÉÏ¡ÀN·ÖÖÓ£ºselect dateadd(mi,-15,getdate())  //²éѯµ±Ç°Ê±¼ä15·ÖÖÓ֮ǰµÄÈÕÆÚ
 ¹Ø¼ü×Ö£ºmssql2005 getdate() 

¸ü¶àÐÐÒµ¶¯Ì¬

¸ü¶à¼¼ÊõÎÄÕÂ

¸ü¶àÍøÕ¾¹«¸æ


¾©ICP±¸13002632ºÅ