Convert a string of date in dd/mm/yyyy format to a date column in SQL Server

Question

I have an excel sheet which contains date in format dd/mm/yyyy. Now when I import it in SQL Server 2008, it takes it as varchar() column. How would I be able to update or convert that varchar() to datetime.

Note: Convert(), Datepart() & Alter Column are not working. Any idea on how would I be able to extract date, month & year from that string?

Any suggestions are welcome.

Download SpreadSheet From Here

You will notice that some dates are aligned differently than others. Dates which are left aligned gets imported as varchar() while dates which are right aligned come as null.

Please could someone try to import it and check if its true or not.

Note - I am using KingSoft's Spreadsheet not MS Excel.

Any idea? Ok guessing, one Excel sheet might be a different from automatic pipeline, but Excel has the concept of types / format on the cells, so it might be best fit to adapt that on Excel level. Are you using the SQL Server Import and Export Wizzard, or export to CSV, or ... (maybe that is clear to others already, but not for me). What you tried reads like on SQL level, as if Excel Transformation not possible, then maybe try SET DATEFORMAT dmy or similar as eg. suggested in Convert Varchar Column to Datetime format - SQL Server? — Dilettant
Format the cells as yyyymmdd and they should import into SQL 2008 without a problem. — user4039065
@Jeeped I tried it but i dont understand why excel is not converting it. — Murtaza Munshi
Maybe click on such a cell in excel and open the format cell dialog, watch the value displayed also in the "entry field" that should be doable on excel level ... then it will work as @Jeeped suggested (I do not have access to such a DB. — Dilettant

StackUser StackUser · Accepted Answer · 2016-06-06T07:38:14

You have to apply custom format for your Date Column in excel before importing it to the Database. Change your date column has Datetime in Database. Once you apply this custom formatting then you can directly import the data to DB Table.

See the sample screenshot.

Convert a string of date in dd/mm/yyyy format to a date column in SQL Server

1 Answers