Format the date in varchar type to mm/dd/yyyy

Question

Hi,

I have a column originally in date format. Later on, I changed it to nvarchar and all the existing date changed to this format "yyyy-mm-dd 00:00:00". How could I update all those dates to mm/dd/yyyy format. Should I use the expression?

Thanks. 

Answer 1

Try this

update <Tablename>
SET start_Date=CONVERT(VARCHAR(10), datecolumn, 101)

--PRashanth

Answer 2

Try this

update <Tablename>
SET start_Date=CONVERT(VARCHAR(10), datecolumn, 101)

--PRashanth

Thanks, but I don't want to change the data type for this column. 

Answer 3

That is not what the suggested query does. It only changes the text inside your varchar column. Try the expression (the CONVERT(...)) in a SELECT statement first and make sure it does what you want. If it does, you can then use it in the UPDATE.

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 4

I would highly recommend you change it back to a date/time type as this is what you are storing.

• MS Sql Server has MANY ways to return dates and times formatted as you want so depending on what you want to see and how you want to see it having the data as date time is infinity better. Case in point is your current predicament.
• Sorting and filtering and comparisons on date/time types is more efficient and less error prone
• Date/time types already have built in validation, trying to enter a date of february 30 is not possible or 27 hours etc.
• You can execute date/time functions like adding a year or subtracting a month which is very useful when you start to make use of the dates/times
• Writing code against databases where dates/times are stored as strings is a horrible experience that I do not wish on anyone!

I currently have to maintain a DB that was originally created by my predecessor with date and times everywhere as varchar and it is a nightmare, Don't Do It especially if you have the chance now to revert back!!! :)

---

-Igor

Answer 5

I should add that I agree with Igor regarding storing datetime data as ... datetime (any of those types).

---

Tibor Karaszi, SQL Server MVP | web | blog