SQL Text to Date error
I have a text column called readdate which is text(6). It is actually a date that is formatted yymmdd. I need to convert this column to date. Here is what I have:
Use HDB
Go
SELECT CUSTOMER
,convert(date,readdate,12)
FROM openquery(UDB,'select * FROM ATABLE');
While it is executing, DATEOFREAD comes up with the format of XXXX-XX-XX, but then errors with this:
Conversion failed when converting date and/or time from character string.
Any suggestions?
Use HDB
Go
SELECT CUSTOMER
,convert(date,readdate,12)
FROM openquery(UDB,'select * FROM ATABLE');
While it is executing, DATEOFREAD comes up with the format of XXXX-XX-XX, but then errors with this:
Conversion failed when converting date and/or time from character string.
Any suggestions?
If you are using SQL Server 2012 or 2014, use Try_Convert instead of Convert; this will return Null if it doesn't succeed, allowing you to diagnose the error further.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Worked great!!
My guess is that the format you are using doesn't match the data string.
Format of 12 is yymmdd
Format of yyyymmdd would be 112.
YYYY-MM-DD in and of itself doesn't appear to be supported.
You could try format 120 - but it may require a time component, not sure it will accept the date without the time component (and not in a position to test that just at the moment).