'Extract specific information from string typed date column using SQL?

I have a date column in my table which is unfortunately in strings and has an inconsistent format(it has a mixed format).
for e.x:
for month march, day 1, year 2022 date can be represented in
-> 1-3-22 or 01-03-2022
how can I extract the day, month, and year separately from the date column?


description:

  • table name -> employee
  • column I'm focusing on -> date -> type-> string
  • language -> SQL
  • DB -> MySQL
    • mysqlsql


    Solution 1:[1]

    In MySQL you can use the STR_TO_DATE functions to parse a string into a date type, then use the DAYOFMONTH, MONTH and YEAR functions to extract the components of interest.

    For example, on current MySQL 8.x versions, using LATERAL to simulate an OUTER APPLY, you can do this:

    create table Example (
  Stringy varchar(10)
);

insert Example (Stringy) values ('1-3-22'), ('01-03-2022');

select Stringy, Datey, dayofmonth(Datey) "Day", month(Datey) "Month", year(Datey) "Year"
from Example
left join lateral (
  select str_to_date(Stringy, '%d-%m-%Y') "Datey"
) S2D on 1=1;

    Which returns the results:

    Stringy Datey Day Month Year
    1-3-22 2022-03-01 1 3 2022
    01-03-2022 2022-03-01 1 3 2022

    Solution 2:[2]

    your data is string value with this form 'Day-Month-Year' that is separated by -, In MySQL you can use SUBSTRING_INDEX to split string based on charterer and change it into your desired result

    -- a test data '01-03-2022'
SELECT
  SUBSTRING_INDEX('01-03-2022','-', -1) AS year1,
  SUBSTRING_INDEX(SUBSTRING_INDEX('01-03-2022', '-', 2),'-', -1) AS month1, 
  SUBSTRING_INDEX('01-03-2022','-', 1) AS day1

    In SQL Server use _PARSENAME(REPLACE) as follows

    -- a test data '01-03-2022'
SELECT  
PARSENAME(REPLACE('01-03-2022' ,'-','.'),1) year1,
PARSENAME(REPLACE('01-03-2022' ,'-','.'),2) month1,
PARSENAME(REPLACE('01-03-2022' ,'-','.'),3) day1

    Sources

    This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

    Source: Stack Overflow

    Solution Source
    Solution 1 AlwaysLearning
    Solution 2

    Related Questions