# How to convert different date formats to unique date format

**URL:** <https://discourse.getdbt.com/t/how-to-convert-different-date-formats-to-unique-date-format/14799>\
**Category:** Help\
**Tags:** best-practice, macros, dbt-core\
**Created:** [August 10, 2024, 7:10am UTC](https://discourse.getdbt.com/t/how-to-convert-different-date-formats-to-unique-date-format/14799 "2024-08-10T07:10:26Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![ysreenu](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/ysreenu/32/3589_2.png) [@ysreenu](https://discourse.getdbt.com/u/ysreenu)\
**Post date:** [August 10, 2024, 7:10am UTC](https://discourse.getdbt.com/t/how-to-convert-different-date-formats-to-unique-date-format/14799/1 "2024-08-10T07:10:26Z")

</div>

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](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/2X/9/91e8793f669737b78c6e13d84a43d4b434f2abff.png)

---

<div class="post-metadata">

**Author:** ![Surya](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/surya/32/3763_2.png) [@Surya](https://discourse.getdbt.com/u/Surya)\
**Post date:** [August 10, 2024, 10:07am UTC](https://discourse.getdbt.com/t/how-to-convert-different-date-formats-to-unique-date-format/14799/2 "2024-08-10T10:07:59Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ysreenu](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/ysreenu/32/3589_2.png) [@ysreenu](https://discourse.getdbt.com/u/ysreenu)\
**Post date:** [August 10, 2024, 10:28am UTC](https://discourse.getdbt.com/t/how-to-convert-different-date-formats-to-unique-date-format/14799/3 "2024-08-10T10:28:10Z")

</div>

Thanks. my target is MYSQL.

---

<div class="post-metadata">

**Author:** ![Surya](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/surya/32/3763_2.png) [@Surya](https://discourse.getdbt.com/u/Surya)\
**Post date:** [August 10, 2024, 10:34am UTC](https://discourse.getdbt.com/t/how-to-convert-different-date-formats-to-unique-date-format/14799/4 "2024-08-10T10:34:02Z")

</div>

can you try this STR\_TO\_DATE(‘2/29/2024’, ‘%m/%d/%Y’)

---

<div class="post-metadata">

**Author:** ![ysreenu](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/ysreenu/32/3589_2.png) [@ysreenu](https://discourse.getdbt.com/u/ysreenu)\
**Post date:** [August 10, 2024, 11:48am UTC](https://discourse.getdbt.com/t/how-to-convert-different-date-formats-to-unique-date-format/14799/5 "2024-08-10T11:48:42Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Surya](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/surya/32/3763_2.png) [@Surya](https://discourse.getdbt.com/u/Surya)\
**Post date:** [August 10, 2024, 12:00pm UTC](https://discourse.getdbt.com/t/how-to-convert-different-date-formats-to-unique-date-format/14799/6 "2024-08-10T12:00:07Z")

</div>

```auto
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.

---

<div class="post-metadata">

**Author:** ![brunoszdl](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/brunoszdl/32/4113_2.png) [@brunoszdl](https://discourse.getdbt.com/u/brunoszdl)\
**Post date:** [August 10, 2024, 7:33pm UTC](https://discourse.getdbt.com/t/how-to-convert-different-date-formats-to-unique-date-format/14799/7 "2024-08-10T19:33:04Z")

</div>

STR\_TO\_DATE(tdate, ‘%d/%m/%Y’)

---

<div class="post-metadata">

**Author:** ![Surya](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/surya/32/3763_2.png) [@Surya](https://discourse.getdbt.com/u/Surya)\
**Post date:** [August 11, 2024, 4:02am UTC](https://discourse.getdbt.com/t/how-to-convert-different-date-formats-to-unique-date-format/14799/8 "2024-08-11T04:02:29Z")

</div>

@brunoszdl the column has two different date formats

---

<div class="post-metadata">

**Author:** ![ysreenu](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/ysreenu/32/3589_2.png) [@ysreenu](https://discourse.getdbt.com/u/ysreenu)\
**Post date:** [August 11, 2024, 1:19pm UTC](https://discourse.getdbt.com/t/how-to-convert-different-date-formats-to-unique-date-format/14799/9 "2024-08-11T13:19:53Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/1X/a7a7ca1fe379aaf90952b0e13118a817babcd14f.png) [@system](https://discourse.getdbt.com/u/system)\
**Post date:** [August 18, 2024, 1:20pm UTC](https://discourse.getdbt.com/t/how-to-convert-different-date-formats-to-unique-date-format/14799/10 "2024-08-18T13:20:30Z")

</div>

Closed on August 18, 2024, 1:20pm UTC

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