CODEWITHSUNDEEP
sql-serverdatetimesql-server-2014datetime-format
Yong Xiang Soh
Yong Xiang Soh

Reputation: 97

Configure SQL to Import Excel Files with Specific Datetime Format

I have a few excel files (exported from very old SQL Server 6.5 database) that have datetime fields in this format:

DD/MM/YYYY HH:MM:SS

I wish to import these excel files into SQL Server 2014. But, SQL Server 2014 accepts excel files with datetime format of:

YYYY/MM/DD HH:MM:SS.FFF

My excel files couldn't be imported. Currently, I'm doing the formatting manually through excel formulas, which is a very tedious process.

Is there a way to configure SQL Server 2014 to take in excel files with a specific datetime format?

Upvotes: 0

Views: 567

Answers (1)

AmyOakes
AmyOakes

Reputation: 61

Have you tried using the import wizard through tasks?

I have managed to import the described data format from excel files. Sample Excel data

Via the Import wizard, once you get to the Source and detestation tab (Column Mappings), click edit mappings, make sure all your date fields from your excel SpreadSheet are set to type > "DateTime". Column Mappings tab

SQL then should import them correctly and then convert them into the YYYY/MM/DD HH:MM:SS.FFF format.

Upvotes: 1

Related Questions