Formatting date of birth using South African ID number

• redbaron7

  SSC Rookie

  Points: 36

  More actions

  August 27, 2018 at 10:13 am

  #2003466

  drew.allen - Monday, August 27, 2018 9:19 AM

  redbaron7 - Monday, August 27, 2018 4:46 AM

  smthembu - Thursday, November 8, 2012 11:49 PM

  Hi AllI need help creating date of birth using ID number the ouput that im looking is a followse.g. RSA ID: 800101 (80 is year, 01 is month and 01 is day) that will be 1980 01 01e.g. RSA ID: 000101 (00 is year, 01 is month and 01 is day) that will be 2000 01 01The 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 198001 Jan 2000Hope the above makes senseThanks

  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

  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()) % 100

  Drew

  oh wow amazing thank you soo much ! 😀

• Sergiy

  SSC Guru

  Points: 110192

  More actions

  September 4, 2018 at 10:24 pm

  #2004462

  I'd simply generate a YYMMDD string for every day in the last 100 years from today, and match those strings to the ones in the table.
  It's 36525 records, ~140k of data in memory, I bet the server won't suffer too much.

  select dateadd(dd, - N, getdate()) [Date],
    convert(varchar(6), dateadd(dd, - N, getdate()) , 12) [YYMMDD]
FROM dbo.TallyGenerator(1, NULL, datediff(dd, DATEADD(YY, -100, GETDATE()), getdate()), 1)
order by dateadd(dd, - N, 0)

  Function dbo.TallyGenerator is published here:
  Script for Tally Generator
  You may use any other script for dynamic Tally table.

  Or - you may use a static Calendar table with a column representing YYMMDD format for every date.

  _____________
  Code for TallyGenerator