SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Formatting date of birth using South African ID number


Formatting date of birth using South African ID number

Author
Message
smthembu
smthembu
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1320 Visits: 1104
Hi All

I need help creating date of birth using ID number the ouput that im looking is a follows

e.g. RSA ID: 800101 (80 is year, 01 is month and 01 is day) that will be 1980 01 01
e.g. RSA ID: 000101 (00 is year, 01 is month and 01 is day) that will be 2000 01 01

The desired format I need is to take the above and create date of birth with the below format as required by the application used.

01 Jan 1980
01 Jan 2000

Hope the above makes sense

Thanks

It's better to fail while trying, rather than fail without trying!!!
Jason-299789
Jason-299789
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4731 Visits: 3232
You have some serious problems and the person responsible for dropping the century part of the date should be taken down a dark ally. (I've been reading too many of Celkos posts, :-D)

As they really should have learnt from the Y2K issues that fixed the data when it was ported from what ever system used a 6 byte field for dates.

However, thats what you have to work with, and its now a proble. Is the RSAId a varchar if Int?

Its its an Int then The first thing you need to do is convert them to character strings (varchar(6)), and pad with leading zeros., if they are already character strings then you should be ok.

This sample code will work for dates between 1950-2049 anything outside this range, say 1940 needs more work, see the date 490101



Declare @dates Table (dob varchar(6))

Insert into @dates
values ('000101'),('490101'),('800101')

Select
convert(Date,dob))
From @dates




For dates before 1950 you are going to be in a world of hurt deciding if 100101 is 01-Jan-2010 or 01-Jan-1910.

heres an example of some logic that will help but it wont work for anyone born 1900-1912


Declare @dates Table (dob varchar(6))

Insert into @dates
values ('000101'),('490101'),('800101'),('100101')


Select
convert(Date, Case When cast(Left(dob,2) as tinyInt)<50
and cast(Left(dob,2) as tinyInt)>right(cast(Year(getdate()) as varchar(4)),2) then '19'
Else '20' End + dob)
from @dates




IF you have an Age on the table it can be used to make an assupmtion, but its very much a problem.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
The Dixie Flatline
The Dixie Flatline
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12386 Visits: 6903
There's a function for what you want: CONVERT()


select convert(varchar(6),convert(date,'01 Jan 1980'),12)
select convert(varchar(6),convert(date,'01 Jan 2000'),12)



The inner CONVERT function turns your string into a DATE character type. The outer string converts it back to a string. The "12" parm at the end says display the date in YYMMDD format. Other formats are available in MSDN Books Online (BOL).

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Jason-299789
Jason-299789
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4731 Visits: 3232
The Dixie Flatline (11/9/2012)
There's a function for what you want: CONVERT()


select convert(varchar(6),convert(date,'01 Jan 1980'),12)
select convert(varchar(6),convert(date,'01 Jan 2000'),12)



The inner CONVERT function turns your string into a DATE character type. The outer string converts it back to a string. The "12" parm at the end says display the date in YYMMDD format. Other formats are available in MSDN Books Online (BOL).


The original problem is that the date is stored as YYMMDD, so how would you cast 120101 as a DOB, is it 01-Jan-2012 or 01-Jan-1900, SQL server will assume its 2012, due to the server settings as it falls in the range 1950-2049.

However its feasible that a person born pre 1950 is still alive, you can therefore assume anything in the range current year to 2049 is 1900, but its also feasible that people are over 99 years old. so you may actually assign the wrong century to the persons Dob.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
The Dixie Flatline
The Dixie Flatline
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12386 Visits: 6903
My mistake. I misread the question.


select convert(char(11),convert(date,'800101'),106)
select convert(char(11),convert(date,'000101'),106)



Jason, I understand your concerns. I agree they are valid and you did well to list them. But I am assuming that smthembu knows his data, so I am simply providing the conversion functions he asked for.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
GilaMonster
GilaMonster
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214236 Visits: 46266
Jason-299789 (11/9/2012)
You have some serious problems and the person responsible for dropping the century part of the date should be taken down a dark ally. (I've been reading too many of Celkos posts, :-D)


Blame the South African government many decades ago.

The format for the South African ID number starts with YYMMDD (date of birth of the ID number holder) and no amount of yelling at the person trying to write some code is going to change the format of the ID number that's used on all official documentation across an entire country.

The ID number is a CHAR(13). YYMMDDGGGGCCC (the numbers G flag the gender, the last 3 contain info on citizenship)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40265 Visits: 19802
Dixie's solution seems simple, but won't work for dates before 1950.
I've done this that would work for any date between today and 100 years in the past (almost).
And a simple test.


DECLARE @tDates table(
RSAID char(8));

WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
E2(N) AS (SELECT 1 FROM E1 a, E1 b)
INSERT @tDates
SELECT RIGHT( '00'+ CAST(ROW_NUMBER() OVER(ORDER BY N) - 1 AS varchar(2)), 2) + '0101'
FROM E2

SELECT RSAID,
CASE WHEN convert(date, RSAID) <= GETDATE() --LEFT( RSAID, 2) <= RIGHT( YEAR(GETDATE()),2)
THEN convert(char(11),convert(date,'20' + RSAID),106)
ELSE convert(char(11),convert(date,'19' + RSAID),106) END
FROM @tDates
ORDER BY 2




Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Sean Pearce
Sean Pearce
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3850 Visits: 3436
There is no reliable way to convert YYMMDD to YYYY\MM\DD and that is why every form I have ever filled in has an entry for ID Number and an entry for D.O.B.



The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Jason-299789
Jason-299789
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4731 Visits: 3232
GilaMonster (11/9/2012)
Jason-299789 (11/9/2012)
You have some serious problems and the person responsible for dropping the century part of the date should be taken down a dark ally. (I've been reading too many of Celkos posts, :-D)


Blame the South African government many decades ago.

The format for the South African ID number starts with YYMMDD (date of birth of the ID number holder) and no amount of yelling at the person trying to write some code is going to change the format of the ID number that's used on all official documentation across an entire country.

The ID number is a CHAR(13). YYMMDDGGGGCCC (the numbers G flag the gender, the last 3 contain info on citizenship)


I didnt know that it was a government standard, but you would have thought they would have bit the bullet and updated thier IT systems 15-20 years ago when the Y2K bug was in full swing. Its not as if its unprecedented in terms of issuing new numbers to people.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
GilaMonster
GilaMonster
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214236 Visits: 46266
Jason-299789 (11/12/2012)
I didnt know that it was a government standard, but you would have thought they would have bit the bullet and updated thier IT systems 15-20 years ago when the Y2K bug was in full swing. Its not as if its unprecedented in terms of issuing new numbers to people.


Why?

It's an identity number, that's all it is and the DOB is just part of generating said ID. There's no assumption that you can extract the full date of birth from the ID correctly in all cases (though if you're dealing with people currently living you can make a general assumption as to the century implied that will be correct in the vast majority of cases), hence why, as Artoo said, all government forms ask for ID number and date of birth (in the format CCYYMMDD usually).

Converting it would require a lot of work, all government forms (in all 11 languages) and all validations done in every IT system that validates ID numbers in the country. I think we have slightly larger problems down here than that...

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search