Extract specific information from string typed date column using SQL?-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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