CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
moe
moe

Reputation: 5249

Convert from varchar into date in SQL Server

This looks easy solution but I can't seem to figure out as to why this is not working for me. I have a column that has data like this:

DateField
----------
12/16/2016
11/06/2016

All I want to do is to convert from varchar into a date column, but I am getting this error:

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

Here is my simple query:

select convert (date, DateField) as convertedField 
from myTable

Upvotes: 4

Views: 30263

Answers (3)

Hadi
Hadi

Reputation: 37313

If working with a specific date format like mm/dd/yyyy You can specify it in Convert() function like the following

CONVERT(DATETIME,DATAFIELD,101)

If it still is not working, use TRY_CONVERT() to get which rows are throwing this exception:

SELECT * 
FROM TBL 
WHERE TRY_CONVERT(DATETIME, DATAFIELD, 101) IS NULL

This will return rows that cannot be converted

TRY_CONVERT() will return NULL if conversion failed

Read more about DateTime formats here: SQL Server CONVERT() Function tutorial

Read TRY_CONVERT MSDN Article

Upvotes: 3

Pரதீப்
Pரதீப்

Reputation: 93694

Nothing wrong with the two examples you have given. There are some bad dates in your table which cannot be converted to date.

Use TRY_CONVERT function for bad dates it will return NULL

select TRY_Convert(date,DateField)
From myTable

You should always store dates in DATE/DATETIME datatype.

If you want to see the records which cannot be converted to date then 

select DateField
From myTable
Where TRY_Convert(date,DateField) IS NULL

Upvotes: 12

Aatish Sai
Aatish Sai

Reputation: 1677

You need to specify the format of date time while formatting. The date in your table is currently in U.S format so you should pass the third argument 101 in your convert function.

SELECT CONVERT(date,[DateField],101) FROM myTable;

Working Fiddle here http://rextester.com/NYKR49788

More info about date time style here: https://msdn.microsoft.com/en-us/library/ms187928.aspx

Upvotes: 0

Related Questions