How to convert different date formats to unique date format

Help
, ,
1

Hi All,

We are exporting data from our ERP system into a CSV file, but we noticed that the date format has changed to (1/31/2022, 1/1/2022, 31/7/2023)
and is stored in the table as a STRING type after processing.

How can we convert these dates into a consistent format, specifically YYYY-MM-DD in dbt model?
Database: MySQL

Note :
I have different approach but not working.
DATE_FORMAT(tdate, ‘%m/%d/%Y’) ,
DATE_FORMAT(tdate,‘%d/%m/%Y’)

s date

2

If your target data warehouse is snowflake then TRY_TO_DATE('2/29/2024', 'MM/DD/YYYY') syntax should fix ur issue.

1 Like
3

Thanks. my target is MYSQL.

4

can you try this STR_TO_DATE(‘2/29/2024’, ‘%m/%d/%Y’)

5

thanks for reply.
i tried same in my dbt model getting error.
written mode like

{{ config(materialized=‘table’) }}

with date_convert as(

select  
a.tdate, 
-- coalesce(
--     DATE_FORMAT(STR_TO_DATE(a.tdate, '%m/%d/%Y'), '%Y-%m-%d'),
--     DATE_FORMAT(STR_TO_DATE(a.tdate, '%d/%m/%Y'), '%Y-%m-%d'),
--     A.TDATE)  new_date,
    case when STR_TO_DATE(a.tdate, '%m/%d/%Y') is not null 
    then DATE_FORMAT(STR_TO_DATE(a.tdate, '%m/%d/%Y'), '%Y-%m-%d') 
    when STR_TO_DATE(a.tdate, '%D/%m/%Y') is not null 
    then DATE_FORMAT(STR_TO_DATE(a.tdate, '%D/%m/%Y'), '%Y-%m-%d')
    
    else null end AS NEW_DATE1

from {{ ref("date_check") }} a

)
select * from date_convert

error
(HY000): Incorrect datetime value: ‘31/7/2023’ for function str_to_date

6 
SELECT 
    STR_TO_DATE(
        CONCAT(
            IF(LENGTH(SUBSTRING_INDEX(date_column, '/', 1)) = 2, SUBSTRING_INDEX(date_column, '/', 1), LPAD(SUBSTRING_INDEX(date_column, '/', 1), 2, '0')),
            '/',
            IF(LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(date_column, '/', 2), '/', -1)) = 2, SUBSTRING_INDEX(SUBSTRING_INDEX(date_column, '/', 2), '/', -1), LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(date_column, '/', 2), '/', -1), 2, '0')),
            '/',
            SUBSTRING_INDEX(date_column, '/', -1)
        ),
        '%d/%m/%Y'
    ) AS parsed_date
FROM 
    your_table;

please try this logic.

7

STR_TO_DATE(tdate, ‘%d/%m/%Y’)

8

@brunoszdl the column has two different date formats

1 Like
9

Thanks for sharing code and modified logic slightly and working for me.

10

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.