Just to add more info about all solution above: SELECT ,Īssuming you don't have a WHERE clause, it is ok, the Convert will try to return all dates even if it is not a valid date like '00000000' (it was in my case). Or ask whoever designed the table (again, really nicely) to fix it. You're never going to be able to convert to a date (never mind convert to a string in a specific format) if your data type choice (or the data type choice of whoever designed the table) inherently allows garbage into your table. Now, convert the Character format yyyymmdd to a Date and DateTime data type using CAST and CONVERT. There's a popular term - garbage in, garbage out. Convert Char yyyymmdd back to Date data types in SQL Server. but this is better done on the client (if at all). When you have a valid date as a string literal, you can use: SELECT CONVERT(CHAR(10), CONVERT(datetime, '20120101'), 120) Now you don't have to worry about the formatting - you can always format as YYYYMMDD or YYYY-MM-DD on the client, or using CONVERT in SQL. ALTER TABLE mydb ALTER COLUMN GRADUATION_DATE DATE Or ask whoever designed the table to FIX THE TABLE. Fix those values, and then FIX THE TABLE. To find the values that are causing issues (so you (or whoever designed this table) can fix them): SELECT GRADUATION_DATE FROM mydbīet you have at least one row. Why are you (or whoever designed this table) storing dates as strings? Do you (or whoever designed this table) also store salary and prices and distances as strings? The error is happening because you (or whoever designed this table) have a bunch of dates in VARCHAR.
0 kommentar(er)
