Formatting date of birth using South African ID number
-
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!!!
-
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 -
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 -
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 -
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 -
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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 @tDatestable(
RSAIDchar(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
-
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 -
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
lol too many :hehe:
I think the OP should maybe shed some more light to get rid of these assumptions
Using the DOB to verify the century is an option
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics -
substring is working well
select
,left([IDNumber],2) as year
,SUBSTRING(Idnumber,3,2) as Month
,SUBSTRING (Idnumber,5,2) as day
,SUBSTRING(Idnumber ,7,4) as gender
,substring (idnumber ,10,3) nationality
from table
kind regards
Michael Gasa
-
cyagasa (10/9/2015)
substring is working wellselect
,left([IDNumber],2) as year
,SUBSTRING(Idnumber,3,2) as Month
,SUBSTRING (Idnumber,5,2) as day
,SUBSTRING(Idnumber ,7,4) as gender
,substring (idnumber ,10,3) nationality
from table
kind regards
Michael Gasa
Substring works but can't guarantee the correct date.
ID Number: 1410095511000
Birth Date 2014/10/09
-- OR --
Birth Date 1914/10/09
- smthembu - Thursday, November 8, 2012 11:49 PM
try this
SELECT abs(case when isnumeric(left(IdNumber,2)) = 1 then left(IdNumber,2)
ELSE null end -try_cast('1'+right(YEAR(getdate()),2) as integer))
FROM dbo.table - redbaron7 - Monday, August 27, 2018 4:46 AM
You do realize that this thread is six years old, don't you?
There are so many issues with your code, it's hard to know where to start.
1) It looks like you are trying to calculate age, but the Original Poster (OP) only wanted to format the DOB.
2) In the subtract operator, you know that the first operand will always be less than 100 and that the second operand will always be greater than 100, so the result will always be negative, which forces you to use the ABS() function. If you just switch the order of the operands, you will always produce a positive number and will not need to use ABS().
3) The TRY_CAST() is completely unnecessary. The value you are passing to that function will ALWAYS be castable to an integer.
4) You have a lot of unnecessary conversions in calculating the value based on the current date. Year returns an integer, but RIGHT() requires a string, which you then TRY_CAST() back to an integer. All of this can be done without any conversions: 100 + YEAR(getdate()) % 100Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply