Date format in Excel file to load to SQL Server

Question

Our business would be providing us a .csv file. One of the columns in the file would be in date format. Now as we know there are many date formats in Excel. The problem is that we need to check whether the date provided is a correct date. It could be in any format like ddmmyyyy, yyyymmdd, dd-mon-yyyy etc basically any format that Excel supports.

We are planning to first load the data in a staging area and the date field would be defined as varchar so that it can accept any data.

Now either using SSIS or via T-SQL, I need to check whether the date provided is actually a date and if it is I need to load it into a different table in YYYYMMDD format.

How do I go about doing the above?

Jose Bagatelli · Accepted Answer · 2018-10-10 03:43:07Z

1

Considering you have your excel data already loaded into a SQL Server table as varchar (you can easily do this using SSIS), something like this would work:

SELECT 
    case when ISDATE(YOUR_DATE) = 1 then CONVERT(int,YOUR_DATE,112) else null end as MyDate
FROM
    YOUR_TABLE

I don't have access to a SQL Server instance at the moment and can't test the code above, so you may need to adapt to your needs, but this is the general idea. You can also do further research on ISDATE and CONVERT functions in SQL Server. You should be able to achieve what you need combining them together.

answered Oct 10, 2018 at 3:43

Jose Bagatelli

1,4111 gold badge20 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sunil Over a year ago

I would try this approach and revert back.

Collectives™ on Stack Overflow

Date format in Excel file to load to SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related