I want to get first day of every corresponding month of current year. For example, if user selects '2010-06-15', query demands to run from '2010-06-01' instead of '2010-06-15'.
我想得到当年每个月的第一天。例如，如果用户选择“2010-06-15”，查询要求从“2010-06-01”而不是“2010-06-15”运行。
Please help me how to calculate first day from selected date. Currently, I am trying to get desirable using following mysql select query:
请帮助我如何计算从选定的日期开始的第一天。目前，我正在尝试使用以下mysql select查询:
Select
DAYOFMONTH(hrm_attendanceregister.Date) >=
DAYOFMONTH(
DATE_SUB('2010-07-17', INTERVAL - DAYOFMONTH('2010-07-17') + 1 DAY
)
FROM
hrm_attendanceregister;
Thanks
谢谢
12 个解决方案
#1
62
Is this what you are looking for:
这就是你要找的:
select CAST(DATE_FORMAT(NOW() ,'%Y-%m-01') as DATE);
#2
26
You can use the LAST_DAY function provided by MySQL to retrieve the last day of any month, that's easy:
您可以使用MySQL提供的LAST_DAY函数来检索任何一个月的最后一天，这很简单:
SELECT LAST_DAY('2010-06-15');
Will return:
将返回:
2010-06-30
Unfortunately, MySQL does not provide any FIRST_DAY function to retrieve the first day of a month (not sure why). But given the last day, you can add a day and subtract a month to get the first day. Thus you can define a custom function:
不幸的是，MySQL没有提供任何FIRST_DAY函数来检索一个月的第一天(不知道为什么)。但是在最后一天，你可以加上一天，减去一个月来得到第一天。因此，您可以定义一个自定义函数:
DELIMITER ;;
CREATE FUNCTION FIRST_DAY(day DATE)
RETURNS DATE DETERMINISTIC
BEGIN
RETURN ADDDATE(LAST_DAY(SUBDATE(day, INTERVAL 1 MONTH)), 1);
END;;
DELIMITER ;
That way:
这种方式:
SELECT FIRST_DAY('2010-06-15');
Will return:
将返回:
2010-06-01
#3
24
There is actually a straightforward solution since the first day of the month is simply today - (day_of_month_in_today - 1):
实际上有一个简单的解决方案，因为这个月的第一天就是今天(day_of_month_in_today - 1):
select now() - interval (day(now())-1) day
Contrast that with the the other methods which are extremely roundabout and indirect.
与其他非常迂回和间接的方法相比。
Also, since we are not interested in the time component, curdate() is a better (and faster) function than now(). We can also take advantage of subdate()'s 2-arity overload since that is more performant than using interval. So a better solution is:
此外，由于我们对time组件不感兴趣，所以curdate()是比now()更好(和更快)的函数。我们还可以利用subdate()的2-arity重载，因为它比使用interval更具有性能。所以更好的解决办法是:
select subdate(curdate(), (day(curdate())-1))
#4
13
This is old but this might be helpful for new human web crawlers XD
这是旧的，但这可能有助于新的人类网络爬虫XD
For the first day of the current month you can use:
本月的第一天你可以使用:
SELECT LAST_DAY(NOW() - INTERVAL 1 MONTH) + INTERVAL 1 DAY;
#5
8
You can use EXTRACT to get the date parts you want:
您可以使用EXTRACT获取所需的日期部分:
EXTRACT( YEAR_MONTH FROM DATE('2011-09-28') )
-- 201109
This works well for grouping.
这对分组很有效。
#6
2
You can use DATE_FORMAT() function in order to get the first day of any date field.
您可以使用DATE_FORMAT()函数来获得任何日期字段的第一天。
SELECT DATE_FORMAT(CURDATE(),'%Y-%m-01') as FIRST_DAY_CURRENT_MONTH
FROM dual;
Change Curdate() with any other Date field like:
更改Curdate()与其他日期字段:
SELECT DATE_FORMAT(purchase_date,'%Y-%m-01') AS FIRST_DAY_SALES_MONTH
FROM Company.Sales;
Then, using your own question:
然后，用你自己的问题:
SELECT *
FROM
hrm_attendanceregister
WHERE
hrm_attendanceregister.Date) >=
DATE_FORMAT(CURDATE(),'%Y-%m-01')
You can change CURDATE() with any other given date.
您可以更改CURDATE()与任何其他给定日期。
#7
1
use date_format method and check just month & year
使用date_format方法并检查一个月和一年。
select * from table_name where date_format(date_column, "%Y-%m")="2010-06"
#8
1
I'm surprised no one has proposed something akin to this (I do not know how performant it is):
我很惊讶没有人提出类似的建议(我不知道它的效果如何):
CONCAT_WS('-', YEAR(CURDATE()), MONTH(CURDATE()), '1')
Additional date operations could be performed to remove formatting, if necessary
如果需要，可以执行其他日期操作来删除格式
#9
0
date_add(subdate(curdate(), interval day(?) day), interval 1 day)
change the ? for the corresponding date
改变吗?在对应的日期
#10
0
This works fine for me.
这对我来说没问题。
date(SUBDATE("Added Time", INTERVAL (day("Added Time") -1) day))
** replace "Added Time" with column name
**用列名替换“添加时间”
Use Cases:
用例:
-
If you want to reset all date fields except Month and Year.
如果要重置除月和年之外的所有日期字段。
-
If you want to retain the column format as "date". (not as "text" or "number")
如果您想保留列格式为“日期”。(不是“文本”或“数字”)
#11
0
Slow (17s): SELECT BENCHMARK(100000000,current_date - INTERVAL (day(current_date)-1) DAY); SELECT BENCHMARK(100000000,cast(DATE_FORMAT(current_date,'%Y-%m-01') as date));
Slow (17s):选择BENCHMARK(100000000,current_date - INTERVAL (day(current_date)-1);选择基准(100000000,演员(DATE_FORMAT(当前日期,% Y - % m-01)日期));
If you don't need a date type this is faster: Fast (6s): SELECT BENCHMARK(100000000,DATE_FORMAT(CURDATE(),'%Y-%m-01')); SELECT BENCHMARK(100000000,DATE_FORMAT(current_date,'%Y-%m-01'));
如果您不需要日期类型，这是更快的:Fast (6s):选择BENCHMARK(100000000,DATE_FORMAT(CURDATE()，'%Y-%m-01');选择基准(100000000,DATE_FORMAT(当前日期,' % Y - % m-01 '));
#12
-1
select big.* from
(select @date := '2010-06-15')var
straight_join
(select * from your_table where date_column >= concat(year(@date),'-',month(@date),'-01'))big;
This will not create a full table scan.
这不会创建完整的表扫描。
#1
62
Is this what you are looking for:
这就是你要找的:
select CAST(DATE_FORMAT(NOW() ,'%Y-%m-01') as DATE);
#2
26
You can use the LAST_DAY function provided by MySQL to retrieve the last day of any month, that's easy:
您可以使用MySQL提供的LAST_DAY函数来检索任何一个月的最后一天，这很简单:
SELECT LAST_DAY('2010-06-15');
Will return:
将返回:
2010-06-30
Unfortunately, MySQL does not provide any FIRST_DAY function to retrieve the first day of a month (not sure why). But given the last day, you can add a day and subtract a month to get the first day. Thus you can define a custom function:
不幸的是，MySQL没有提供任何FIRST_DAY函数来检索一个月的第一天(不知道为什么)。但是在最后一天，你可以加上一天，减去一个月来得到第一天。因此，您可以定义一个自定义函数:
DELIMITER ;;
CREATE FUNCTION FIRST_DAY(day DATE)
RETURNS DATE DETERMINISTIC
BEGIN
RETURN ADDDATE(LAST_DAY(SUBDATE(day, INTERVAL 1 MONTH)), 1);
END;;
DELIMITER ;
That way:
这种方式:
SELECT FIRST_DAY('2010-06-15');
Will return:
将返回:
2010-06-01
#3
24
There is actually a straightforward solution since the first day of the month is simply today - (day_of_month_in_today - 1):
实际上有一个简单的解决方案，因为这个月的第一天就是今天(day_of_month_in_today - 1):
select now() - interval (day(now())-1) day
Contrast that with the the other methods which are extremely roundabout and indirect.
与其他非常迂回和间接的方法相比。
Also, since we are not interested in the time component, curdate() is a better (and faster) function than now(). We can also take advantage of subdate()'s 2-arity overload since that is more performant than using interval. So a better solution is:
此外，由于我们对time组件不感兴趣，所以curdate()是比now()更好(和更快)的函数。我们还可以利用subdate()的2-arity重载，因为它比使用interval更具有性能。所以更好的解决办法是:
select subdate(curdate(), (day(curdate())-1))
#4
13
This is old but this might be helpful for new human web crawlers XD
这是旧的，但这可能有助于新的人类网络爬虫XD
For the first day of the current month you can use:
本月的第一天你可以使用:
SELECT LAST_DAY(NOW() - INTERVAL 1 MONTH) + INTERVAL 1 DAY;
#5
8
You can use EXTRACT to get the date parts you want:
您可以使用EXTRACT获取所需的日期部分:
EXTRACT( YEAR_MONTH FROM DATE('2011-09-28') )
-- 201109
This works well for grouping.
这对分组很有效。
#6
2
You can use DATE_FORMAT() function in order to get the first day of any date field.
您可以使用DATE_FORMAT()函数来获得任何日期字段的第一天。
SELECT DATE_FORMAT(CURDATE(),'%Y-%m-01') as FIRST_DAY_CURRENT_MONTH
FROM dual;
Change Curdate() with any other Date field like:
更改Curdate()与其他日期字段:
SELECT DATE_FORMAT(purchase_date,'%Y-%m-01') AS FIRST_DAY_SALES_MONTH
FROM Company.Sales;
Then, using your own question:
然后，用你自己的问题:
SELECT *
FROM
hrm_attendanceregister
WHERE
hrm_attendanceregister.Date) >=
DATE_FORMAT(CURDATE(),'%Y-%m-01')
You can change CURDATE() with any other given date.
您可以更改CURDATE()与任何其他给定日期。
#7
1
use date_format method and check just month & year
使用date_format方法并检查一个月和一年。
select * from table_name where date_format(date_column, "%Y-%m")="2010-06"
#8
1
I'm surprised no one has proposed something akin to this (I do not know how performant it is):
我很惊讶没有人提出类似的建议(我不知道它的效果如何):
CONCAT_WS('-', YEAR(CURDATE()), MONTH(CURDATE()), '1')
Additional date operations could be performed to remove formatting, if necessary
如果需要，可以执行其他日期操作来删除格式
#9
0
date_add(subdate(curdate(), interval day(?) day), interval 1 day)
change the ? for the corresponding date
改变吗?在对应的日期
#10
0
This works fine for me.
这对我来说没问题。
date(SUBDATE("Added Time", INTERVAL (day("Added Time") -1) day))
** replace "Added Time" with column name
**用列名替换“添加时间”
Use Cases:
用例:
-
If you want to reset all date fields except Month and Year.
如果要重置除月和年之外的所有日期字段。
-
If you want to retain the column format as "date". (not as "text" or "number")
如果您想保留列格式为“日期”。(不是“文本”或“数字”)
#11
0
Slow (17s): SELECT BENCHMARK(100000000,current_date - INTERVAL (day(current_date)-1) DAY); SELECT BENCHMARK(100000000,cast(DATE_FORMAT(current_date,'%Y-%m-01') as date));
Slow (17s):选择BENCHMARK(100000000,current_date - INTERVAL (day(current_date)-1);选择基准(100000000,演员(DATE_FORMAT(当前日期,% Y - % m-01)日期));
If you don't need a date type this is faster: Fast (6s): SELECT BENCHMARK(100000000,DATE_FORMAT(CURDATE(),'%Y-%m-01')); SELECT BENCHMARK(100000000,DATE_FORMAT(current_date,'%Y-%m-01'));
如果您不需要日期类型，这是更快的:Fast (6s):选择BENCHMARK(100000000,DATE_FORMAT(CURDATE()，'%Y-%m-01');选择基准(100000000,DATE_FORMAT(当前日期,' % Y - % m-01 '));
#12
-1
select big.* from
(select @date := '2010-06-15')var
straight_join
(select * from your_table where date_column >= concat(year(@date),'-',month(@date),'-01'))big;
This will not create a full table scan.
这不会创建完整的表扫描。