Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Formatting date of birth using South African ID number Expand / Collapse
Author
Message
Posted Thursday, November 8, 2012 11:49 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 5:42 AM
Points: 953, Visits: 875
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!!!
Post #1382837
Posted Friday, November 9, 2012 12:24 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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, )

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
Post #1382854
Posted Friday, November 9, 2012 12:29 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:13 PM
Points: 3,997, Visits: 6,050
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? -- Stephen Stills
Post #1382857
Posted Friday, November 9, 2012 12:36 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Post #1382867
Posted Friday, November 9, 2012 7:53 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:13 PM
Points: 3,997, Visits: 6,050
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? -- Stephen Stills
Post #1383025
Posted Friday, November 9, 2012 8:59 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:18 PM
Points: 42,437, Visits: 35,492
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, )


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 2008, MVP
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

Post #1383072
Posted Friday, November 9, 2012 9:13 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:52 PM
Points: 3,325, Visits: 7,172
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1383078
Posted Monday, November 12, 2012 3:32 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 11:15 AM
Points: 824, Visits: 2,655
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
Post #1383585
Posted Monday, November 12, 2012 4:27 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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, )


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
Post #1383603
Posted Monday, November 12, 2012 5:31 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:18 PM
Points: 42,437, Visits: 35,492
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 2008, MVP
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

Post #1383637
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse