计算一个月内的天数

On my web page I have 2 text boxes from which I enter the Year and Month. Can you tell me a mysql query which will get the number of days in that month. I want to use that in my code behind and depending on that number of days I need to do other stuff. I searched and found something like this

在我的网页上,我有2个文本框,我可以从中输入年份和月份。你能告诉我一个mysql查询,它会得到那个月的天数。我想在我的代码中使用它,并根据我需要做其他事情的天数。我搜索并找到了这样的东西

SELECT DAY(LAST_DAY('2012-02-1'));

This is my code behind

这是我背后的代码

public void search(object sender, EventArgs e)
        {
            string cnnString = "Server=localhost;Port=3307;Database=leavesystem;Uid=root;Pwd=ashish";
            MySqlConnection cnx = new MySqlConnection(cnnString);
            cnx.Open();
            string cmdText = "Select DAY(LAST_DAY(CAST(CONCAT(@'" + year.value +"', '-', @'"+ month.value+"', '-', 1) AS DATE))) ";
            MySqlCommand cmd = new MySqlCommand(cmdText, cnx);

         }

This gives me the number of days but I don't have the date. I have 2 text box values year and month. Can you give me the query for that?

这给了我天数,但我没有日期。我有2个文本框值年和月。你能问我一下吗?

2 个解决方案

#1

Supposing you have the two parameters:

假设您有两个参数:

SET @y = 2013;
SET @m = 5;

@y will be the year, and @m will be the month.

@y将成为年份,而@m将是月份。

Since every month has its first day, you can set the day to 1 as constant

由于每个月都有第一天,因此您可以将日期设置为1作为常量

SELECT DAY(LAST_DAY(CAST(CONCAT(@y, '-', @m, '-', 1) AS DATE)));

By the way, I'm using session variables, I think you will be using INPUT parameters, I guess it will be the same.

顺便说一下,我正在使用会话变量,我想你会使用INPUT参数,我想它会是一样的。

EDIT 1

If the month passed in the query is MONTHNAME, you can change it in the code-behind already. But if you insist, here is the query to get the MONTHNUMBER:

如果查询中传递的月份是MONTHNAME,则可以在代码隐藏中更改它。但是如果你坚持,这里是获取MONTHNUMBER的查询:

SET @m = MONTH(STR_TO_DATE('June', '%M'));

Here is the final query:

这是最终查询:

SET @y = 2013;
SET @m = 'June';

SELECT DAY(LAST_DAY(CAST(CONCAT(@y, '-', MONTH(STR_TO_DATE(@m, '%M')), '-', 1) AS DATE)));

#2

What if you give by defaults the 01 first of every month in the query, don't know your programming language but just to give an idea

如果您在查询中默认提供每月的01,那么该怎么办,不知道您的编程语言,只是为了给出一个想法

var date_vaiable=year_variable+"-"month_variable+"-01"; //+ sign is for concat concept

SELECT DAY(LAST_DAY(date_vaiable));

#1