Create SQL Randomized Date of Birth

Does anyone have any SQL code to automatically generate randomized birth dates, where the date of birth is less than today? Please add Date of Birth Range parameters, eg: from 18 to 70 years old.

Is there any inline SQL or function to do this?

We are trying to obfuscate our column [Date of Birth].

Thanks,

This will add a random number of days to 1st of January, 1900:

SELECT DATEADD(DAY, CONVERT(int, CRYPT_GEN_RANDOM(2)), '1900-01-01T00:00:00');

According to the Microsoft Docs, CRYPT_GEN_RANDOM "returns a cryptographic random number generated by the Crypto API (CAPI). The output is a hexadecimal number of the specified number of bytes."

So CRYPT_GEN_RANDOM(2) returns a two-byte number in the range of 0x0000 to 0xFFFF, when converted into a signed-integer and "added" to 1900-01-01, will result in dates in the range of 1900-01-01 to 2079-06-06.

For a table named dbo.MyTable, with a column named [Date of Birth], this will update all column values to randomly generated dates:

UPDATE dbo.MyTable
SET [Date of Birth] = DATEADD(DAY, CONVERT(int, CRYPT_GEN_RANDOM(2)), '1900-01-01T00:00:00');

One could reverse the logic such that you have people of various ages from 0 days old to approximately 59 years old with this:

UPDATE dbo.MyTable
SET [Date of Birth] = DATEADD(DAY, (1 - CONVERT(int, CRYPT_GEN_RANDOM(2)) / 3), GETDATE());

The following example will randomly choose birth dates resulting in ages between 10 and 20 years old:

DECLARE @MinAge int;
DECLARE @MaxAge int;

SET @MinAge = 10;
SET @MaxAge = 20;
UPDATE dbo.MyTable
SET [Date of Birth] = DATEADD(DAY
    , (1 - (CONVERT(int, CRYPT_GEN_RANDOM(2)) % ((@MaxAge - @MinAge) * 365)))
    , CONVERT(date, DATEADD(YEAR, 1 - @MinAge, GETDATE()))
    );

StackExchange Database Administrators Q#205468, answer score: 23