How to modify incorrect date in a SQL Server database column in C#

Question

I have a column in my SQL Server database of nvarchar datatype, and I store dates with ####/##/## format in it. With my mistake some of dates store with ##/##/#### format.

How can I modify all dates in my database to format ####,##,##?

Example: 01/01/2016 to 2016/01/01

Is the bad data mm/dd/yyyy (American) or dd/mm/yyyy (Nearly everywhere else) — CDove
– CDove, Commented Jan 12, 2017 at 13:16
Their is a datatype called date for saving dates then why are you trying to compromise with nvarchar — sujith karivelil
– sujith karivelil, Commented Jan 12, 2017 at 13:17
If you're in the mood to fix the data, why not do it right and switch to using a datetime2 or date column? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jan 12, 2017 at 13:17
Don't store dates in your database as strings. Use the proper data type. Only convert to a string at the point where you actually need to display to the user. — mason
– mason, Commented Jan 12, 2017 at 13:22
The problem is the field type, not the format. You should store dates in date-typed fields (date, datetime etc). Fix your table schema, not your data — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jan 12, 2017 at 13:24

Community · Accepted Answer · 2020-06-20 09:12:55Z

5

Use CONVERT function. Write simple SQL query:

UPDATE YourTable 
-- get Date from nvarchar and format to yyyy/mm/dd
SET DateColumn = CONVERT(nvarchar,CONVERT(DATE, DateColumn, 103), 111)
-- only in rows that can be parsed from dd/mm/yyyy format
WHERE TRY_CONVERT(DATE, DateColumn, 103) IS NOT NULL

103 - dd/mm/yyyy format. 111 - yyyy/mm/dd format. check this article

But actually you really should store your dates in DATE type column not in NVARCHAR.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jan 12, 2017 at 13:27

teo van kot

12.5k10 gold badges41 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

R.F.V Over a year ago

i get this error : 'TRY_CONVERT' is not a recognized built-in function name.

teo van kot Over a year ago

@R.F.V what version of MS SQL do you use? It's 2012 version function

R.F.V Over a year ago

Microsoft SQL Server 2012 - 11.0.2100.60

teo van kot Over a year ago

@R.F.V strange... I tested on my 2012 db - woks fine

Collectives™ on Stack Overflow

How to modify incorrect date in a SQL Server database column in C#

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related