Convert Nvarchar DD/MM/YYYY to YYYY-MM-DD in SQL Query

Question

Hi I have a column in my 'Users' table called 'PersonalDetails_DOB'.

The data type is a NVARCHAR(10) and the 'format' the data is currently in DD/MM/YYYY.

I want to change the format to YYYY.MM.DD or YYYY-MM-DD, how would I do this?

I have already tried a running a query in SQL:

 SELECT CONVERT(nvarchar(10), PersonalDetails_DOB, 102) as
 'PersonalDetails_DOB' FROM Users;

Nothing happens and it doesn't change any date from e.g. 06/02/1967 to 1967.02.06

Solution 1

Store dates as dates, not strings. I recommend you pull the value as a date and not a string:

SELECT CONVERT(date, PersonalDetails_DOB, 103) as PersonalDetails_DOB
FROM Users;

You are safer using try_convert():

SELECT TRY_CONVERT(date, PersonalDetails_DOB, 103) as PersonalDetails_DOB
FROM Users;

To find the bad values, you can do:

select PersonalDetails_DOB
from users
where  TRY_CONVERT(date, PersonalDetails_DOB, 103) is null;

You can convert back a string if you want.

I would recommend that you fix the data structure. This should work:

update users
    set PersonalDetails_DOB = CONVERT(date, PersonalDetails_DOB, 103);

alter users alter column PersonalDetails_DOB date;

Solution 2

You need two convert() function :

SELECT PersonalDetails_DOB, 
       CONVERT(VARCHAR(10), CONVERT(DATE, PersonalDetails_DOB, 103), 102)
FROM Users;

Comments

[Thorsten Kettner]

Your query is treating PersonalDetails_DOB as if it were a date, while it's a string actually. Best would be to really store it as a DATE. As long as it's stored as a string (NVARCHAR), you would either have to use string manipulation in order to swap string parts or convert it to date first before converting it into another string then.

[Suraj Kumar]

@Zack You need to first update the values in date format and then you can change the data type of column. See my answer.

[Zack Antony Bucci]

So do i need to change the data type to 'date' instead of Nvarchar? that means i'll loose all of the data in the column? It is Nvarchar for a reason, can't remember why and changing it to DateTime will mess up alot of things within the Web App. Also using your code I get the following error: Conversion failed when converting date and/or time from character string.

[Gordon Linoff]

@ZackAntonyBucci . . . Your data is messed up. You will need to spend time fixing your data and your application if you want to use the date-of-birth. Good lesson on why you want to use proper types to store data.

[Zack Antony Bucci]

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

[Thorsten Kettner]

@Zack Antony Bucci: Then your table contains data that doesn't match DD/MM/YYYY. This is why you should have stored the dates as dates and not as strings in the first place. I suggest you change your table design (rename the column, add a date column PersonalDetails_DOB, fill the new column with an update, delete the old column).