Date conversion issue.: yyyy/MM/dd to dd/MM/yyyy

Question

I have a date column where the date format is

2010-04-14

in SQL Server. Is there any possible way to retrieve the date format as

14/04/2010

in a select statement?

Answer 1

Try this:

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

Source Link

Addendum 1:

As described in my comment, you need to convert your field to a DATETIME type before the above will work. This example should work on your SQL Server:

SELECT CONVERT(VARCHAR(10), CAST('2010-04-14' AS DATETIME), 103) AS [DD/MM/YYYY]

If you get this exception:

Msg 242, Level 16, State 3, Line 1 The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Then you need to change your date field to be in YYYY-MM-DD Format, EG: 2010-14-04

OR

Add the following line before the select statement:

SET DATEFORMAT MDY -- Input dates are in the MM/DD/YYYY format, change to DMY to handle UK dates

Example:

SET DATEFORMAT MDY;

SELECT CONVERT(VARCHAR(10), CAST('2010-04-14' AS DATETIME), 103) AS [DD/MM/YYYY]

Answer 2

Another way to solve your problem:

select substring(columnName, 9, 2) + '/' + substring(columnName, 6, 2)  + '/' + substring(columnName, 1, 4)