Question 20027 in Mysql Question posted in
The official MySQL documentation can be found here.

Get first and last dates based on only Month and Year combination – Mysql

We have a couple of staging tables for Data in mysql db that we read from a input file. In the first table, the data is dumped as is. This data is then processed and grouped in certain ways and result is inserted in the second staging table.
One of the fields has original values in ‘MMM YYYY’ format, e.g. ‘Sep 2021’, ‘Jul 2022’ etc. and during processing we need to calculate the first and last date of this combination and resultant fields will be called startdate and enddate, in format YYYY-MM-dd so Sep 2021 should result in 2021-09-01 as startdate and 2021-09-30 as enddate respectively.

I had tried:

DATE_FORMAT(STR_TO_DATE(CONCAT('01 ',delivery),'%d %b %Y'),'%m/%d/%y') as startdate

and

DATE_FORMAT(LAST_DAY(STR_TO_DATE(delivery,'%b %Y')),'%m/%d/%y') as enddate

But it is not working. I get an error: Sep 2021 is not valid for STR_TO_DATE

Can someone provide the the correct, simplest and most efficient way of achieving this?

Tags:

2

Answers


  1. 0

    The error message you’re getting suggests that the STR_TO_DATE() function is not able to parse the input date string in the format ‘MMM YYYY’. This is because the ‘%b’ format specifier in the function expects the month abbreviation to be in all uppercase letters, but your input strings have the first letter capitalized.

    To fix this, you can convert the first letter of the month abbreviation to uppercase before applying the STR_TO_DATE() function. Here’s an example query that does this and calculates the start and end dates as requested:

        SELECT delivery, 
       DATE_FORMAT(STR_TO_DATE(CONCAT('01 ', UPPER(LEFT(delivery, 3)), SUBSTRING(delivery, 4)), '%d %b %Y'), '%Y-%m-%d') AS startdate, 
       DATE_FORMAT(LAST_DAY(STR_TO_DATE(CONCAT('01 ', UPPER(LEFT(delivery, 3)), SUBSTRING(delivery, 4)), '%d %b %Y')), '%Y-%m-%d') AS enddate
FROM staging_table_2;

    In this query, the UPPER() function is used to convert the first letter of the month abbreviation to uppercase, and the LEFT() and SUBSTRING() functions are used to extract the month abbreviation and year from the ‘MMM YYYY’ format.

    The resulting date strings are formatted using the %Y-%m-%d format specifier, which gives dates in the format ‘YYYY-MM-DD’. This format is equivalent to the ISO 8601 standard and is a good choice for storing dates in a database.

    Login or Signup to reply.
  2. 0

    Your solution almost correct, try this :

    This is for start date :

    SELECT DATE_FORMAT(STR_TO_DATE(CONCAT('01 ', 'Sep 2021'),'%d %b %Y'),'%Y-%m-%d') as startdate

    This for end date, we take the converted date by STR_TO_DATE then apply LAST_DAY on it

    SELECT DATE_FORMAT(LAST_DAY(STR_TO_DATE(CONCAT('01 ', 'Sep 2021'),'%d %b %Y')),'%Y-%m-%d') as enddate

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search