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?

Solution

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]

Comments

[Arulkumar]

Invoice date data type is date or varchar?

[Alex K.]

It fails because you attempt to convert 'ddmmyyyy' into a date & that input format is not supported - it's interpreted as 'yyyymmdd' - e.g. with mm == 20

[Zohar Peled]

What is the data type of columnvalue?

[Admin]

Not helpful. GETDATE() retrieves current computer date and not Invoice date value in database table. Also, the AS [DD/MM/YYYY] just changes the column name title.

[Arulkumar]

I gave AS [DD/MM/YYYY] for your understanding, you can place AS [InvoiceDate] or what ever you are required. As I already asked in comment, what is the data type of invoice date and for which sample date it throw error?

[Admin]

So the first "convert" returns results so the column must already be a date data type. But format is still YYYMMDD and not required 103 British format of DD/MM/YYY.

[Admin]

So this worked, but no slashes or dashes that is needed.

[Admin]

CONVERT(char(10),CAST(right(convert(varchar,invoicedate),2)+‌​substring(convert(VA‌​RCHAR,invoicedate),5‌​,2) + left(convert(VARCHAR,invoicedate),4) AS VARCHAR) ,103) AS "Invoice Date"

[Zohar Peled]

Convert with style 103 returns dd/mm/yyyy with slashes. 105 returns dd-mm-yyyy with dashes. If you've got a different value you did it wrong.