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.
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…
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.
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.
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.
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.
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;
CREATE FUNCTION dbo.My_DateAdd(@ProductID date)
-- Returns original date plus a month and a day
DECLARE @ret date;
SELECT @ret = dateadd(day, 1, dateadd(month, 1, @ProductID));
IF (@ret IS NULL)
SET @ret = '1900-01-01';
And this is how I called it from FDM:
This worked as expected, no errors - what errors were you seeing?
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;
GOCREATE FUNCTION dbo.[maskdOB]( -- Add the parameters for the function here @DOB as datetime)RETURNS DatetimeASBEGINDeclare @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 functionRETURN @maskedDt