Search code examples
pervasive-sql

Adding days to varchar field in Pervasive SQL

I'm trying to copy Production data into test environment. In one of the table I have a Date of birth field defined as VARCHAR(10). Since this is a sensitive column , we need to randomize the values in this date field so that original DOB are not exposed to developers. Can you please guide me how to randomize the date or add days to this date field in pervasive sql.

Solution

  • If you are using a newer version of PSQL (I tried on v12), you could potentially use something like this algorithm to generate a new date:

    create table dtTest (f1 varchar(10));
insert into dtTest (f1) values ('2016-02-28');
select F1, left(convert(dateadd(day, truncate(rand() * 10, 0), dateadd(month, truncate(rand() * 10, 0), convert(f1, SQL_DATE))), SQL_VARCHAR), 10) from dtTest;

    It can even be turned into an update statement and run against your table:

    update <Table> set <DateField> = left(convert(dateadd(day, truncate(rand() * 10, 0), dateadd(month, truncate(rand() * 10, 0), convert(<DateField>, SQL_DATE))), SQL_VARCHAR), 10)