Conversion failed when converting date from character string

Question

I am trying to rearrange Invoice date field of YYYMMDD into short date format DD/MM/YYYY or DD-MM-YYYY UK/British format in a SELECT statement.

Performing on MSSQL server 2008 R2

SQL

CONVERT(date,CAST(columnvalue AS VARCHAR),103)

Produces YYYY-MM-DD

CAST(right(convert(varchar,columnvalue),2)+substring(convert(VARCHAR,columnvalue ),5,2) + left(convert(VARCHAR,columnvalue ),4)AS VARCHAR)

Produces DDMMYYY

CONVERT(date,CAST(right(convert(varchar,columnvalue),2)+substring(convert(VARCHAR,columnvalue),5,2) + left(convert(VARCHAR,columnvalue),4)AS VARCHAR),103) 

ERROR

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

What am I doing wrong?

Answer 1

The following will be helpful:

SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]

SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY]

Answer 2

Depending on the data type of the column you are attempting to convert, there are different ways to solve this.

Assuming the column is already one of the date types (date/datetime/datetime2) the conversion is as simple as it gets - all you need is to choose the correct style parameter.

For British/French (dd/mm/yyyy) you use 103:

SELECT CONVERT(char(10), dateOrdateTimeValue, 103)

If the source column is a string column (char/varchar/nchar/nvarchar), and the string representation format of the date is yyyymmdd, you can safely cast it to date since yyyymmdd will always be interpreted correctly by SQL Server, and then use convert just like before:

SELECT CONVERT(char(10), CAST(stringRepresentationOfDateAsYYYYMMDD As Date), 103)

Note: only ISO8601 formats (yyyymmdd | yyyy-mm-dd) is guaranteed to be interpreted correctly regardless of culture settings to date data type (Bewrae: There's a bug in the DateTime data type with the second format).

If the source column is an int, you first cast to char, then to date, and finally use convert:

SELECT CONVERT(char(10), CAST(CAST intRepresentationOfDateAsYYYYMMDD as char(8)) As Date), 103)