'SQL Convert numbers into Date

I'm a beginner in SQL and I need your help please!

I have a project which is convert Social Security Numbers into birth date.

In the database, I have a column MatriculeSecuriteSociale and in this column, I have Social Security Numbers for all employees and all numbers look like this YYYYMMDDXXXXX.

My questions are:

  1. How to convert into date format as YYYY.MM:DD?
  2. How to calculate the age of people over 45?

I can convert the values to the format YY.MM.DD but I don't know how to apply to all the column MatriculeSecuriteSociale and then calculate (point 2)...

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
sqlsql-serverssms


Solution 1:[1]

If you use your SELECT as a subselect, your outer SELECT can use the "calculated" date field.

SELECT subquery.Birthdate,
       DATEDIFF(YEAR, subquery.Birthdate, GETDATE()) as Age
  FROM (SELECT CAST(STUFF(STUFF(LEFT(@MatriculeSecuriteSociale, 8), 5, 0, '-'), 8, 0, '-') AS DATETIME) As Birthdate) subquery
WHERE DATEDIFF(YEAR, subquery.Birthdate, GETDATE()) > 45

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 IngoB

Related Questions