Search code examples
sql-servert-sqldata-cleaning

Convert a column with both MM-DD-YYYY & DD-MM-YYYY dates to just MM-DD-YYYY?

I am having trouble finding a solution to getting the date column on my table to properly convert/change from a mix of mm-dd-yyyy and dd-mm-yyyy to just mm-dd-yyyy.

The proper dates in the column with the mm-dd-yyyy format are separated by "/" and the rest with the dd-mm-yyyy format are separated with "-". The data I am using comes from the Kaggle Walmart Dataset.

Date
5/2/2010
12/2/2010
19-02-2010
26-02-2010
5/3/2010
12/3/2010
19-03-2010
26-03-2010

I imported the dataset into SQL Server as a flat file CSV which imported the data as varchar(50) and I could manipulate the other columns easily to other data types (DECIMAL and INT) but the [Date] column has been giving me trouble and I receive this message after trying to CAST the data to 'Date' data type:

Conversion failed when converting date and/or time from character string.

My logic is telling me to separate the column into three columns using "/" and "-" as delimiters but it would still leave me with the issue of the mm-dd and dd-mm mix up.

I have two main questions from this.

  1. Is the message I am receiving due to the dates being mixed so SQL is considering the column as a character string?

  2. Is there an optimal way to get all of the dates into a mm-dd-yyyy format?

Any assistance for solving is greatly appreciated!

Solution

  • Assuming you are correct in your assumption:

    The proper dates in the column with the mm-dd-yyyy format are separated by "/" and the rest with the dd-mm-yyyy format are separated with "-".

    Then you can do

    ISNULL(
  TRY_CONVERT(date, YourValue, 105),
  TRY_CONVERT(date, YourValue, 101)
)

    See the list of data and time styles in the docs.