CODEWITHSUNDEEP
sql-serversql-server-2008
Tina
Tina

Reputation: 3

need to change date format in Varchar column

I have DOB varchar column which contains date value. I am getting dates in mm/dd//yyyy format. I have to change format of all these values to yyyymmdd format in DOB column. I can't use datetime datatype for DOB column as per customer’s demand.

Please suggest what can be done.

Upvotes: 0

Views: 990

Answers (3)

Aaron Bertrand
Aaron Bertrand

Reputation: 280490

UPDATE dbo.tablename
  SET DOB = CONVERT(CHAR(8), CONVERT(DATETIME, DOB, 101), 112);

Of course there is no guarantee that all of the data in there right now is valid m/d/y data. Someone could have entered d/m/y, or something that is entirely not a date at all.

You can get close by fixing any rows that come back from this query:

SELECT DOB FROM dbo.tablename WHERE ISDATE(DOB) = 0;

Of course this won't identify any rows where someone entered 09/05/2013 and meant May 9th, not September 5th.

This also won't prevent future strings from being entered that are not valid dates (or not the dates that the user who entered them expected them to represent).

Strongly consider having your customer read these posts. We have date-related data types for a reason.

Upvotes: 4

user275683
user275683

Reputation:

My original answer was

SELECT CONVERT(VARCHAR, CONVERT(DATETIME,'12/01/2012'), 112)

but I was typing too slow. So another alternative would be, but this is not a robust solution because it relies on formatting to be the same.

 DECLARE @MyDate VARCHAR(12) = '12/25/2012';
 SELECT SUBSTRING(@MyDate, 7, 4) + SUBSTRING(@MyDate, 4, 2) + SUBSTRING(@MyDate, 0, 3)

Upvotes: 0

b.runyon
b.runyon

Reputation: 154

You should be able to do ..... 

SELECT CAST( YEAR(DOB_Column) AS VARCHAR(4)) +  
       CAST( LPAD(MONTH(DOB_Column),2,0) AS VARCHAR(2)) + 
       CAST( LPAD(DAY(DOB_Column),2,0) AS VARCHAR(2)) AS MyDate 
FROM MyTable

Upvotes: 0

Related Questions