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)+LEF T(date_of_ birth,2)+S UBSTRING(d ate_of_bir th,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!
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)+LEF
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
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER