Link to home
Start Free TrialLog in
Avatar of robthomas09
robthomas09

asked on

SQL - convert string mm/dd/yyyy to yyyymmdd - SQL Server 2005

Hello experts,

I have a table with a date_of_birth column in varchar(10) format.  I have a need to convert the string into a similar string format shown  below:

Currently I have:

table: person

person_id         date_of_birth
123                   3/9/1963
124                   11/22/1935
125                   5/6/1943
126                   11/22/1935
127                   10/4/1955

I would like to end up with:

person_id         date_of_birth
123                   19630309                       --3/9/1963
124                   19351122                      --11/22/1935
125                   19430506                       --5/6/1943
126                   19351122                       --11/22/1935
127                   19551004                       --10/4/1955

When i try and do something like
update person
set date_of_birth = RIGHT(date_of_birth,4)+LEFT(date_of_birth,2)+SUBSTRING(date_of_birth,4,2)

it gives me trouble because of cases where there are single digit months or days like 5/22/2011where the month is a single digit, or 11/5/2004 where the day is a single digit.

Thoughts?

Thanks!
ASKER CERTIFIED SOLUTION
Avatar of AndrewSkoraro
AndrewSkoraro

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Qlemo
Qlemo
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of robthomas09
robthomas09

ASKER

Thanks!