Swapping day and month for a date column in SQL

Question

I need your help. I would like to transform (re-calculate) a date column by randomly changing only the month and day and keeping only the year. This task aims to anonymize the date of birth for certain people.

For example: For this original date: '1996-07-04' to be '1996-12-01'. (let's say. it could be anything else instead of day and month).

OriginalDateBirth	AnonymDatebirth
1996-07-04	1996-12-01
1955-01-01	1955-04-09

Do you think you can help me with a sql statement, function that can implement it?

Thanks :)

Answer 1

Here's one method you can try. This doesn't try to cater for varying days in a month but perhaps it might be "good enough" for your use-case.

(If you really wanted to cater for days 29-31 you could apply the months and then use a case expression on the result to control the mod value for the day)

with sampledata as (
  select Convert(date, '19960704') DOB union all
  select Convert(date, '19550101') DOB
)
select dob, 
  DateFromParts( Year(dob), (Abs(Checksum(NewId())) % 12)+1, (Abs(Checksum(NewId())) % 28)+1) AnonDob, 
from sampledata

DB Fiddle