Test Data Manager

  • 1.  Need advise on FDM to mask DOB to custom format

    Posted Oct 10, 2018 04:14 PM

    In the FDM, I have a requirement to mask the date column in a specific format of adding one month and one day to the existing DOB. When I add 2 to 3 conditions, it is working fine. When I add more conditions(as i need to consider leap year, non leap year, month end, etc) it is doing double masking. Please help me arrive at a optimal solution(without restartability as client is not ok to add any additional column in the exising table). Thanks in advance.



  • 2.  Re: Need advise on FDM to mask DOB to custom format
    Best Answer

    Posted Oct 12, 2018 11:38 AM

    In FDM, try the SQLFUNCTION() and then your target DBMS' own dateadd() function (or equivalent) - the dateadd() function will take care of leap years, month end, etc…



  • 3.  Re: Need advise on FDM to mask DOB to custom format

    Posted Oct 15, 2018 09:49 AM

    Thank you so much for your suggestion. I explored that option of using the SQLFUNCTION() mask type. Though the CA documentation says that you can use either the in built or user defined function, am having trouble when I used UDF. I tried all possible combination but still having trouble(function as such works fine from SSMS, but giving error while giving it in FDM).

     

    Yes, as you mentioned, DATEADD() would take care of all given scenarios but when I give a fixed no: of days to add it is going to give me wrong output for month end dates. Please provide me with just one sample of how to use a UDF in FDM which could be of great help to me. 

     

    Once again thank you for taking time to answer my question.  



  • 4.  Re: Need advise on FDM to mask DOB to custom format

    Posted Oct 16, 2018 07:55 AM

    Hi Sethu

     

    I haven't had time to test the UDF yet, but I did try this today:

     

    dateadd(day, 1, dateadd(month, 1, date_of_birth))

     

    It adds a month to the original date_of_birth value, and then adds a day to that - this gives repeatable and predictable results around the month and year end boundaries.

     

    Sample Results:

     

    1971-09-30 becomes     1971-10-31

    1971-10-31 becomes     1971-12-01

     

    If I have misunderstood your requirement, please let me know.

     

    Out of interest, I'll try a UDF later when I have some free time.

     

    Regards,

                  Adrian



  • 5.  Re: Need advise on FDM to mask DOB to custom format

    Posted Oct 16, 2018 11:04 AM

    You rock, AdrianJohnsonUK !!! This gives the exact result I was looking for!

     

    But as you have mentioned, please let me know when you get some free time about the UDF usage in FDM. Appreciate all your help!! Thank you so much again. 

     

    Regards

    Sethu



  • 6.  Re: Need advise on FDM to mask DOB to custom format

    Posted Oct 16, 2018 11:10 AM

    You're very welcome - we're here to help :-)

     

    For completeness, here is the UDF I created (same functionality as above, just for testing):

     

    IF OBJECT_ID (N'dbo.My_DateAdd', N'FN') IS NOT NULL 

        DROP FUNCTION My_DateAdd; 

    GO 

    CREATE FUNCTION dbo.My_DateAdd(@ProductID date) 

    RETURNS date  

    AS  

    -- Returns original date plus a month and a day 

    BEGIN 

        DECLARE @ret date; 

        SELECT @ret = dateadd(day, 1, dateadd(month, 1, @ProductID)); 

         IF (@ret IS NULL)  

            SET @ret = '1900-01-01'; 

        RETURN @ret; 

    END;

     

    And this is how I called it from FDM:

     

    This worked as expected, no errors - what errors were you seeing?

     

    • Note the ‘dbo.…' qualifiers used everywhere - is that what is missing for you?

     

    HTH,

                  Adrian

     



  • 7.  Re: Need advise on FDM to mask DOB to custom format

    Posted Oct 16, 2018 11:34 AM

    Not sure what I was missing. The same function is now working as expected. Below is the content of my function(ofcourse elaborated, which is not needed though). Bottom line, it is working fine now.

     

    Once again thank you so much for taking time and effort to check this for me!!

     

    IF OBJECT_ID (N'dbo.maskdOB', N'FN') IS NOT NULL

    DROP FUNCTION maskdOB;

    GO
    CREATE FUNCTION dbo.[maskdOB]
    (
    -- Add the parameters for the function here
    @DOB as datetime
    )
    RETURNS Datetime
    AS
    BEGIN
    Declare @maskedDt datetime

    Select @maskedDt = case when month(@DOB) = 2 and year(@DOB)%4=0 and day(@DOB) = 29 then dateadd(day,1,@DOB)
    when month(@DOB) = 2 and year(@DOB)%4<>0 and day(@DOB) = 28 then dateadd(day,1,@DOB)
    when month(@DOB) in (1,3,5,7,8,10,12) and day(@DOB) = 31 then dateadd(day,1,@DOB)
    when month(@DOB) in (4,6,9,11) and day(@DOB) = 30 then dateadd(day,1,@DOB)
    when month(@DOB) = 2 and day(@DOB) not in (28,29) then dateadd(day,29,@DOB)
    when month(@DOB) in (1,3,5,7,8,10,12) and day(@DOB) <> 31 then dateadd(day,32,@DOB)
    when month(@DOB) in (4,6,9,11) and day(@DOB) <> 30 then dateadd(day,31,@DOB)
    else '' end
    -- Return the result of the function
    RETURN @maskedDt

    END