***mysql中查詢今天、昨天、上個月sql語句

標籤：

今天 select * from 表名 where to_days(時間欄位名) = to_days(now());昨天Select * FROM 表名 Where TO_DAYS( NOW( ) ) - TO_DAYS( 時間欄位名) <= 17天Select * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(時間欄位名)近30天Select * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(時間欄位名)本月Select * FROM 表名 Where DATE_FORMAT( 時間欄位名, ‘%Y%m‘ ) = DATE_FORMAT( CURDATE( ) , ‘%Y%m‘ )上一月Select * FROM 表名 Where PERIOD_DIFF( date_format( now( ) , ‘%Y%m‘ ) , date_format( 時間欄位名, ‘%Y%m‘ ) ) =1#查詢本季度資料select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(now());#查詢上季度資料select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));#查詢本年資料select * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW());#查詢上年資料select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year)); 查詢當前這周的資料 SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,‘%Y-%m-%d‘)) = YEARWEEK(now());查詢上周的資料SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,‘%Y-%m-%d‘)) = YEARWEEK(now())-1;查詢當前月份的資料select name,submittime from enterprise   where date_format(submittime,‘%Y-%m‘)=date_format(now(),‘%Y-%m‘)查詢距離當前現在6個月的資料select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();查詢上個月的資料select name,submittime from enterprise   where date_format(submittime,‘%Y-%m‘)=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),‘%Y-%m‘)select * from ` user ` where DATE_FORMAT(pudate, ‘ %Y%m ‘ ) = DATE_FORMAT(CURDATE(), ‘ %Y%m ‘ ) ;select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,‘%y-%m-%d‘)) = WEEKOFYEAR(now())select * from user where MONTH (FROM_UNIXTIME(pudate, ‘ %y-%m-%d ‘ )) = MONTH (now())select * from [ user ] where YEAR (FROM_UNIXTIME(pudate, ‘ %y-%m-%d ‘ )) = YEAR (now())and MONTH (FROM_UNIXTIME(pudate, ‘ %y-%m-%d ‘ )) = MONTH (now())select * from [ user ] where pudate between 上月最後一天and 下月第一天where   date(regdate)   =   curdate();select   *   from   test   where   year(regdate)=year(now())   and   month(regdate)=month(now())   and   day(regdate)=day(now())SELECT date( c_instime ) ,curdate( )FROM `t_score`WHERE 1LIMIT 0 , 30

　　

MSSQL擷取昨天，本周，本月。。。

原文連結地址：http://blog.knowsky.com/186111.htm特別說明下：以下統計本周資料時，星期天是作為下周的第一天，而不是本周最後一天，因此你把星期天作為本周最後一天時，你需要在getDate()的基礎上減一天,如dateadd(‘day‘, -1, getDate())本周：select * from table where datediff(week,C_CALLTIME,getdate())=0 --C_CALLTIME 為日期欄位本月：select * from table where datediff(Month,C_CALLTIME,getdate())=0 --C_CALLTIME 為日期欄位本季：select * from table where datediff(qq,C_CALLTIME,getdate())=0前半年1-6，後半年7-12：select * from table where datepart(mm,C_CALLTIME)/7 = datepart(mm,getdate())/7昨天select convert(varchar(10),getdate() - 1,120)明天select convert(varchar(10),getdate() + 1,120)最近七天select * from tb where 時間欄位 >= convert(varchar(10),getdate() - 7,120)隨後七天select * from tb where 時間欄位 <= convert(varchar(10),getdate() + 7,120) and 時間欄位 >= 時間欄位convert和dateadd函數結合使用就可以了。用datediff(day,時間列,getdate())上月select * from tb where month(時間欄位) = month(getdate()) - 1本月select * from tb where month(時間欄位) = month(getdate())下月select * from tb where month(時間欄位) = month(getdate()) + 1--如果是在表中查詢--昨天Select * From TableName Where DateDiff(dd, DateTimCol, GetDate()) = 1--明天Select * From TableName Where DateDiff(dd, GetDate(), DateTimCol) = 1--最近七天Select * From TableName Where DateDiff(dd, DateTimCol, GetDate()) <= 7--隨後七天Select * From TableName Where DateDiff(dd, GetDate(), DateTimCol) <= 7--上周Select * From TableName Where DateDiff(wk, DateTimCol, GetDate()) = 1--本周Select * From TableName Where DateDiff(wk, DateTimCol, GetDate()) = 0--下周Select * From TableName Where DateDiff(wk, GetDate(), DateTimCol ) = 1--上月Select * From TableName Where DateDiff(mm, DateTimCol, GetDate()) = 1--本月Select * From TableName Where DateDiff(mm, DateTimCol, GetDate()) = 0--下月Select * From TableName Where DateDiff(mm, GetDate(), DateTimCol ) = 1--------------------------------------------------------本周select * from tb where datediff(week , 時間欄位 ,getdate()) = 0上周select * from tb where datediff(week , 時間欄位 ,getdate()) = 1下周select * from tb where datediff(week , 時間欄位 ,getdate()) = -1--------------------------------------------------------1.現在我需要得到只是日期部分，時間部分不要，SQL怎麼寫？select convert(varchar(10),getdate(),120)2.求以下日期SQL:昨天select convert(varchar(10),getdate() - 1,120)明天select convert(varchar(10),getdate() + 1,120)最近七天select * from tb where 時間欄位 >= convert(varchar(10),getdate() - 7,120)隨後七天select * from tb where 時間欄位 <= convert(varchar(10),getdate() + 7,120) and 時間欄位 >= 時間欄位

　　

***mysql中查詢今天、昨天、上個月sql語句