赞
踩
- -- 本月第一天
- select date_add(curdate(),interval-day(curdate())+1 day) as date
- -- 本月最后一天
- select last_day(curdate()) as date
- -- 查询今天的日期
- select CURDATE() as '当前日期';
- -- 查询当前时间
- select NOW() as '当前时间';
- -- 明天日期
- select DATE_SUB(curdate(),INTERVAL -1 DAY) AS tomorrow
- -- 下个月第一天
- select date_add(curdate() - day(curdate()) +1,interval 1 month )
- -- 当前月已过几天
- select day(curdate())
- -- 当前月天数
- select day(date_add( date_add(curdate(),interval 1 month),interval -day(curdate()) day ))
-
- -- 最近7天日期
- SELECT @s :=@s + 1 as `index`, DATE(DATE_ADD(CURRENT_DATE, INTERVAL @s DAY)) AS `date`
- FROM mysql.help_topic,(SELECT @s := -7) temp
- WHERE @s < 0
- ORDER BY 'date'
-
- -- 当天以后7天日期
- SELECT @s :=@s + 1 as `index`, DATE(DATE_SUB(CURRENT_DATE, INTERVAL @s DAY)) AS `date`
- FROM mysql.help_topic,(SELECT @s := -7) temp
- WHERE @s < 0
- ORDER BY 'date'
-
- 一、查询本周日期
- SELECT @a :=@a + 1 as `index`, DATE(ADDDATE(CURRENT_DATE, INTERVAL @a DAY)) AS `date`
- FROM mysql.help_topic,(SELECT @a := 0) temp
- WHERE @a < 6 - WEEKDAY(CURRENT_DATE)
- UNION
- SELECT @s :=@s - 1 as `index`, DATE(DATE_SUB(CURRENT_DATE, INTERVAL @s DAY)) AS `date`
- FROM mysql.help_topic,(SELECT @s := WEEKDAY(CURRENT_DATE) + 1) temp
- WHERE @s > 0
- ORDER BY `date`
-
- 二、查询当月所有日期
- select date from (
- SELECT DATE_FORMAT(DATE_SUB(last_day(curdate()), INTERVAL xc-1 day), '%Y-%m-%d') as date
- FROM (
- SELECT @xi:=@xi+1 as xc from
- (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) xc1,
- (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) xc2,
- (SELECT @xi:=0) xc0
- ) xcxc) x0 where x0.date >= (select date_add(curdate(),interval-day(curdate())+1 day))
- ORDER BY `date`
-
- 三、查询往后一个月日期
- SET @days = TIMESTAMPDIFF( DAY, ADDDATE( date( now( ) ),- 31 ), now( ) );
- SET @d = - 1;
- SELECT
- @d := @d + 1 AS 'index',
- ADDDATE( ADDDATE( date( now( ) ),- 0), @d ) AS date
- FROM
- (
- SELECT a
- FROM
- ( SELECT '1' AS a UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION SELECT '6' ) AS a
- JOIN ( SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION SELECT '6' ) AS b ON 1
- LIMIT 31
- ) d
- WHERE @d < @days
-
- 四、查询本年时间,按月份格式返回
- select concat((select year(NOW())), '-01') tj_date
- union select concat((select year(NOW())), '-02') tj_date
- union select concat((select year(NOW())), '-03') tj_date
- union select concat((select year(NOW())), '-04') tj_date
- union select concat((select year(NOW())), '-05') tj_date
- union select concat((select year(NOW())), '-06') tj_date
- union select concat((select year(NOW())), '-07') tj_date
- union select concat((select year(NOW())), '-08') tj_date
- union select concat((select year(NOW())), '-09') tj_date
- union select concat((select year(NOW())), '-10') tj_date
- union select concat((select year(NOW())), '-11') tj_date
- union select concat((select year(NOW())), '-12') tj_date
-
- 五、查询近一年时间,按日期格式返回
- SELECT DATE_SUB(CURDATE(), INTERVAL dummy DAY) AS date
- FROM
- (SELECT @rownum:=@rownum+1 AS dummy
- FROM information_schema.tables
- JOIN (SELECT @rownum:=0) r
- LIMIT 365) dates;
-
- 六、查询去年时间,按月份格式返回
- select concat((select year(DATE_SUB(NOW(), INTERVAL 1 YEAR))), '-01') tj_date
- union select concat((select year(DATE_SUB(NOW(), INTERVAL 1 YEAR))), '-02') tj_date
- union select concat((select year(DATE_SUB(NOW(), INTERVAL 1 YEAR))), '-03') tj_date
- union select concat((select year(DATE_SUB(NOW(), INTERVAL 1 YEAR))), '-04') tj_date
- union select concat((select year(DATE_SUB(NOW(), INTERVAL 1 YEAR))), '-05') tj_date
- union select concat((select year(DATE_SUB(NOW(), INTERVAL 1 YEAR))), '-06') tj_date
- union select concat((select year(DATE_SUB(NOW(), INTERVAL 1 YEAR))), '-07') tj_date
- union select concat((select year(DATE_SUB(NOW(), INTERVAL 1 YEAR))), '-08') tj_date
- union select concat((select year(DATE_SUB(NOW(), INTERVAL 1 YEAR))), '-09') tj_date
- union select concat((select year(DATE_SUB(NOW(), INTERVAL 1 YEAR))), '-10') tj_date
- union select concat((select year(DATE_SUB(NOW(), INTERVAL 1 YEAR))), '-11') tj_date
- union select concat((select year(DATE_SUB(NOW(), INTERVAL 1 YEAR))), '-12') tj_date
-
- 七、查询近6个月时间,按月份格式返回
- select date_format(CURDATE(), '%Y-%m') AS tj_date
- union select date_format((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') as tj_date
- union select date_format((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') as tj_date
- union select date_format((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') as tj_date
- union select date_format((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') as tj_date
- union select date_format((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') as tj_date
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。