3

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?

Alex K.
  • 171,639
  • 30
  • 264
  • 288
Shazam42
  • 31
  • 1
  • 1
  • 3

2 Answers2

2

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]
Arulkumar
  • 12,966
  • 14
  • 47
  • 68
  • 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. – Shazam42 Jun 04 '19 at 16:54
  • 1
    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? – Arulkumar Jun 04 '19 at 16:56
0

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)
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • 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. – Shazam42 Jun 10 '19 at 21:29
  • So this worked, but no slashes or dashes that is needed. – Shazam42 Jun 10 '19 at 21:40
  • CONVERT(char(10),CAST(right(convert(varchar,invoicedate),2)+substring(convert(VARCHAR,invoicedate),5,2) + left(convert(VARCHAR,invoicedate),4) AS VARCHAR) ,103) AS "Invoice Date" – Shazam42 Jun 10 '19 at 21:41
  • 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. – Zohar Peled Jun 11 '19 at 04:31