INSERT INTO CALENDAR_INFO (COUNTRY,CDR_CODE,DATE_NAME, YEAR,MONTH,WEEK,DAY,CDR_DATE,CREATOR,CREATE_DATE,CREATE_INST) SELECT 'CN', 'CN01', --CONVERT函数将@START_DATE转换成指定的格式YYYY-MM-DD CONVERT(CHAR(10),@START_DATE,111) DATE_NAME, --DATENAME获取@START_DATE的年份并用COVERT转换为...
select date_add(@dt, interval 1 microsecond); select date_add(@dt, interval 1 week); select date_add(@dt, interval 1 month); select date_add(@dt, interval 1 quarter); select date_add(@dt, interval 1 year); select date_add(@dt, interval -1 day); -- sub 1 day MySQL adddate(...
group by YEAR(savetime)*10+((MONTH(savetime)-1) DIV 3) +1; 五、分组查询 1、年度分组 2、月度分组 3、先按年度分组,再按月度分组 4、按年月分组 SELECT count(ArticleId), date_format(FROM_UNIXTIME( `BlogCreateTime`),'%y%m') sdate FROM `blog_article` group by sdate 结果: count( Art...
select * from 表名 where YEAR(create_date)=YEAR(NOW()); 13、查询上年数据 select * from 表名 where year(create_date)=year(date_sub(now(),interval 1 year)); 14、查询当前这周的数据 SELECT * FROM 表名 WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now()); 15、查询...
select*from`ht_invoice_information`whereyear(create_date)=year(date_sub(now(),interval1year)); 查询当前这周的数据 SELECTname,submittimeFROMenterpriseWHEREYEARWEEK(date_format(submittime,'%Y-%m-%d'))=YEARWEEK(now()); 查询上周的数据 SELECTname,submittimeFROMenterpriseWHEREYEARWEEK(date_format(submit...
LAST_DAY Return the last day of the month for the argument LOCALTIME(), LOCALTIME Synonym for NOW() LOCALTIMESTAMP, LOCALTIMESTAMP() Synonym for NOW() MAKEDATE() Create a date from the year and day of year MAKETIME() Create time from hour, minute, second MICROSECOND() Return...
mysql>selectDAYOFYEAR('2020-4-24'),DAYOFMONTH('2020-4-24'); 11、获取年份的函数 YEAR(date) :返回 date 对应的年份 代码语言:javascript 复制 mysql>selectYEAR('20-04-24'),YEAR('98-04-24'); 12、获取季度的函数 QUARTER(date) :返回 date 对应的一年中的季度值 ...
前两month的最后一天: SELECT LAST_DAY(now() – interval 2 month) 当前quarter的第一天: select concat(date_format(LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) + interval QUARTER(CURDATE())*3-3 month),’%Y-%m-’),’01′) 当前quarter的最后一天: ...
mysql> SELECT the_date, YEAR(the_date), MONTHNAME(the_date), DAYOFMONTH(the_date) FROM test ; +---+---+---+---+ | the_date | YEAR(the_date)|MONTHNAME(the_date)| DAYOFMONTH(the_date)| +---+---+---+---+ | 2021-11-02| 2021 | November | 2 | | 2022-01-...
-- 获取订单的年份SELECTYEAR(order_date)ASyearFROMorders;-- 获取订单的月份SELECTMONTH(order_date)ASmonthFROMorders;-- 获取订单的日期SELECTDAY(order_date)ASdayFROMorders; 1. 2. 3. 4. 5. 6. 7. 8. 以上代码将返回orders表中每个订单的年、月和日的值。