Home / Questions / 350
Accepted
fthiella
Asked: 2020-12-04 05:49:38 +0800 CST 2020-12-04 05:49:38 +0800 CST

Function that returns the date for the first day of the month

  • 772

MySQL has the LAST_DAY(date) function that returns the date for the last day of the month:

SELECT LAST_DAY(fecha) AS ultimo_dia
FROM table

How can I get the date for the first day of the month?

mysql

8 Answers

  1. Best Answer

    Two alternatives occur to me:

    SELECT fecha - INTERVAL DAYOFMONTH(fecha) - 1 DAY;

SELECT SUBDATE(fecha, DAYOFMONTH(fecha) - 1);

    Both involve only 2 function calls. They will surely differ in efficiency based on whether the INTERVALis calculated faster than the SUBDATE.

    • 12

  2. Apparently there is no function that returns the first day of the month, you should create it, and it goes something like this:

    DELIMITER ;;
CREATE FUNCTION FIRST_DAY(day DATE)
RETURNS DATE DETERMINISTIC
BEGIN
  RETURN ADDDATE(LAST_DAY(SUBDATE(day, INTERVAL 1 MONTH)), 1);
END;;
DELIMITER ;

    and you would use it like this

    SELECT FIRST_DAY('2010-06-15');

    font

    • 6

  3. first day of the month 

    select now() - interval (day(now())-1) day

    First day of the following month : it is simply the last day + 1

    select adddate(last_day(curdate()), 1)

    Source: Extracted from StacOverFlow available at https://stackoverflow.com/questions/3009896/get-the-first-and-last-date-of-next-month-in-mysql

    • 5

  4. A quick option: create a new date by concatenating the year and month of the date, and specify a 1 for the day. The code would be like this:

    SELECT DATE(CONCAT_WS('-', YEAR(fecha), MONTH(fecha), '1')) AS primer_dia
FROM   mitabla

    You could save that code in a function:

    CREATE FUNCTION FIRST_DAY(fecha DATE) 
RETURNS DATE
RETURN DATE(CONCAT_WS('-', YEAR(fecha), MONTH(fecha), '1'))

    And call the function directly from the SELECT:

    SELECT fecha, FIRST_DAY(fecha) 
FROM   mitabla
    • 4

  5. Testing in my DB, I think this could work applied to a table:

    SELECT 
    LAST_DAY(fecha_creacion - INTERVAL 1 MONTH) + INTERVAL 1 DAY 
FROM 
    tabla;

    Update:

    It only seems to work fine if you pass it a specific record:

    SELECT 
    LAST_DAY(fecha_creacion - INTERVAL 1 MONTH) + INTERVAL 1 DAY 
FROM 
    tabla 
WHERE 
    campo_id=id;

    If you wanted to know, for example, from a table the first day of the month with respect to all records, you could add MAXto the query:

    SELECT 
    LAST_DAY(MAX(fecha_creacion) - INTERVAL 1 MONTH) + INTERVAL 1 DAY   
FROM 
    tabla;
    • 3

  6. just implement it like this:

     SELECT DATE_FORMAT(fecha, '%Y-%m-01') as primer_dia
 FROM table

    without the need for additional functions.

    • 3

  7. I found a solution thanks to the guides mentioned above and I managed to obtain the first day of the month in the following way:

    (CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY))

    With this, what it does is that it obtains the date omitting the time, now if what you want is the date and time, then replace CURRENT_DATE with NOW().

    CURRENT_DATE returns 2017-09-01, while NOW() returns: 2017-09-01 02:10:39

    An example query to serve as a guide:

    SELECT saldoInicial FROM ventas WHERE fecha = (CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY))

    In itself it is more orderly since it returns the type DATE.

    • 1

  8. There is a very portable way that works for me....

    SELECT DATE_FORMAT(now(), '%Y-%m-01');

    Answer: 2019-08-01

    • 1