SQL Convert numbers into Date

LTVM 21 Reputation points
2022-05-13T15:08:16.53+00:00

Hi all,
I’m a beginner in SQL and I need your help please!
I have a project which is convert Social Security Numbers into birthdate.
In Database I have a column called “MatriculeSecuriteSociale” and in this column , I have Social Security Numbers for all employees and all numbers look like this YYYYMMDDXXXXX
My question is:
1- how to convert into date format as YYYY.MM:DD
2- how to calculate the age of people over 45
I can convert in to the format YY.MM.DD but I don’t know how to apply to all the column and then calculate the point 2…
Thanks in advance
Kind regards


declare @MatriculeSecuriteSociale as char(36)
select @MatriculeSecuriteSociale=MatriculeSecuriteSociale from Salarie
SELECT
STUFF(STUFF(LEFT(@MatriculeSecuriteSociale, 8), 5, 0, '.'), 8, 0, '.') As Birthdate

Result:

Birthdate

1984.12.31

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,484 questions
0 comments No comments
{count} votes

Accepted answer
  1. Naomi 7,361 Reputation points
    2022-05-13T15:22:37.427+00:00

    Try

    ;with cte as (select *, cast(left(MatriculeSecuriteSociale,8) as date) as [BirthDay] from Salarie)
    
    select *,
    format(BirthDay, 'yyyy.MM.dd') as [FormattedBirthday],
     datediff(year, BirthDay, CURRENT_TIMESTAMP) as Age from cte where BirthDay > dateadd(year,-45, CURRENT_TIMESTAMP) -- find people who is older than 45 years old
    
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Jingyang Li 5,891 Reputation points
    2022-05-13T15:44:25.373+00:00
    create table Salarie (id int,  MatriculeSecuriteSociale char(36))
    
    insert into Salarie values (1,'YYYYMMDDXXXXX'),(2,'1965061012345'),(3,'1970012954321'), (4,'2000011512345')
    
    ;with mycte as (
    Select id, try_cast(left(MatriculeSecuriteSociale,8) as date) birthdate,
    Format(try_cast(left(MatriculeSecuriteSociale,8) as date),'yy.MM.dd' ) dt
    from Salarie)
    Select * from mycte
    WHERE 
    CASE WHEN
     (DATEADD(year,DATEDIFF(year, [birthdate]  ,current_timestamp) , [birthdate]) > current_timestamp)
    THEN DATEDIFF(year, [birthdate]  ,current_timestamp) -1
    ELSE DATEDIFF(year, [birthdate]  ,current_timestamp) END>=45
    
    
    drop table Salarie
    

  2. LTVM 21 Reputation points
    2022-05-13T15:45:32.8+00:00

    Thank you so much NaomiNNN!

    it's working like a charm

    I see and understand better now what I was missing to arrive at the solution! A big thank-you!

    King Regards

    LTVM

    0 comments No comments