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

Question

I had a query that was working but after importing again one table I see the following problem when converting a Varchar into DateTime.

I have the following problem when running the following query:

select FORMAT(convert (datetime,date)  ,   'ddMMyyyy')  from  kat.[dbo].[myTable]

If I try the following I see the same problem:

SELECT convert(datetime, date, 126) from kat.[dbo].[myTable]

The dates that I have in the main table follow the same format:

2017-09-01

EDit with Data Screenshot for the Format:

MAny thanks in advance,

Kat

So what format is the date in? Is it yyyy-MM-dd, yyyy-dd-MM or ddMMyyyy? — HoneyBadger, Oct 31 '17 at 13:58
The date is as mentioned the following format : 2017-09-01 -> yyyy-MM-dd — Katherine99, Oct 31 '17 at 14:00
You did not mention that, it could have been yyyy-dd-MM, especially since it is already different from your first mentioned format. — HoneyBadger, Oct 31 '17 at 14:01
If I use select FORMAT(convert (datetime,date) , 'yyyy-MM-dd') from kat.[dbo].[myTable] I see the error too — Katherine99, Oct 31 '17 at 14:12

sepupic · Answer 1 · 2017-10-31T15:32:59.183

0

The dates that I have in the main table follow the same format:
2017-09-01

There are some stings in your table that have different format.

Try to catch them with this code:

select *
from kat.[dbo].[myTable]
where dt not like '[1-9][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9]';

In future, don't store datetime values as strings, use datetime/date.

UPDATE

So it's not true that your strings are like '2017-09-01' Here is my example with your date:

declare @t table (dt varchar(20));
insert into @t values ('2017-09-01');

select *
from @t
where dt not like '[1-9][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9]';

My code returns no rows because '2017-09-01' reflects the format you inicated, and if it returns all rows in your case, the format is different.

edited Oct 31 '17 at 15:32

answered Oct 31 '17 at 15:13

sepupic

8,409
1
9
20

Hi sepupic, This query shows all the records of the table. Is there a better way to convert these strings into datetime? – Katherine99 Oct 31 '17 at 15:18
So what is the problem? They miss a separator ot what? Can you give an example? – sepupic Oct 31 '17 at 15:28
I updated my answer with your string, my code does not return the row '2017-09-01', so your strings are not of that format. I repeat my question, WHAT is real format of your strings? – sepupic Oct 31 '17 at 15:34
varchar is a DATA TYPE, not a format. Format is yyyy-mm-dd, if belief to OP, but it is not REAL format of your strings. Why cannot you post an example of your string if you are not able to determine a format? – sepupic Oct 31 '17 at 16:24
I have edited the main post with the screenshot of the data – Katherine99 Oct 31 '17 at 16:31
Can you please execute select len(date) from kat.[dbo].[myTable] – sepupic Oct 31 '17 at 17:44

score 0 · Answer 2 · answered Oct 31 '17 at 17:46

I actually think strings cannot be converted directly to dates (at least not when you use CAST)... Why not convert the string to an int first?

Assuming your column is named [Date], I would stick with:

SELECT
cast(DATEADD(DAY, cast([Date] as int), -2) as date) as [Date]
FROM kat.[dbo].[myTable]

If you have a different column name, here is the template:

SELECT
cast(DATEADD(DAY, cast([MyColumnName] as int), -2) as date) as [MyColumnName]
FROM kat.[dbo].[myTable]

Alternatively, I've also updated your code using CONVERT (which honestly I rarely every use so it could be wrong)

SELECT convert(datetime, convert(int, [Date]), 126) from kat.[dbo].[myTable]

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

2 Answers2