question

LTVM-3316 avatar image
0 Votes"
LTVM-3316 asked LTVM-3316 commented

SQL Convert numbers into Date

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-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NaomiNNN avatar image
0 Votes"
NaomiNNN answered NaomiNNN edited

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JingyangLi avatar image
0 Votes"
JingyangLi answered LTVM-3316 commented
 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
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you JingyangLi

0 Votes 0 ·
LTVM-3316 avatar image
0 Votes"
LTVM-3316 answered Viorel-1 commented

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.