Set variable to get dateformating in azure data factory

Question

I have a SQL query that declares a start date and end date using the following code:

declare @StartDate date = CASE WHEN CAST(GETDATE() as date) = DATEFROMPARTS(YEAR(GETDATE()), 1, 1) 
                            THEN DATEFROMPARTS(YEAR(GETDATE())-1, 1, 1)
                            ELSE DATEFROMPARTS(YEAR(GETDATE()), 1, 1)
                            END
                            
declare @EndDate date = CASE WHEN month(CAST(GETDATE() as date)) = 1    
                                 and  left(right(cast(GETDATE() as nvarchar(10)),2),1) = '0'                        
                                 THEN DATEFROMPARTS(YEAR(GETDATE())-1,12,31)                                
                                    else case when left(right(cast(GETDATE() as nvarchar(10)),2),1) <> '0'
                                                then EOMONTH( GETDATE())
                                                else EOMONTH(DATEADD(month, -1, GETDATE()))                     
                                        end
                                    end

I now need to convert this to a set variable expression in Azure Data Factory. For the start date, I tried the following code:

@formatDateTime(iif(formatDateTime(utcnow(), 'yyyy-MM-dd') == concat(formatDateTime(utcnow(), 'yyyy'), '-01-01'), concat(sub(int(formatDateTime(utcnow(), 'yyyy')), 1), '-01-01'), concat(formatDateTime(utcnow(), 'yyyy'), '-01-01')), 'yyyy-MM-dd')

However, I received an error stating "Please use math functions (e.g. 'add') rather than symbol operators (e.g. '+')." How can I modify my expression to fix this error?

LOGIC for enddate:

when its new year (january) and begin of the month (days between 01-09) then it should return current year minus 1 and month 12 and day 31. Otherwise when month can be anything except january and its end of the month ( sql expression left(right(cast(GETDATE() as nvarchar(10)),2),1) <> '0') return the last day of the current month, else (meaning if its the begin of the month not january give me last day of the previous month.

For example:

1st CASE WHEN -- If its January and day range is between 1st and 9th January then return December (last year) 31st.

else case when -- if its not a January( but any other month) and day range is between 10th and last day of the month return last day of that that current month. But if its not a January a( but any other month) and day rane is from 1st to 9th return the last of the current month - 1 month ( last day of the last month).

For example: today is 2023 06 19 it should return 2023 06 30. If it was 2023 06 09 it should have returned 2023 05 31.

Answer 1

"Please use math functions (e.g. 'add') rather than symbol operators (e.g. '+')."

Expression for StartDate:

@formatDateTime(if(equals(formatDateTime(utcnow(), 'yyyy-MM-dd'),concat(formatDateTime(utcnow(), 'yyyy'), '-01-01')),concat(string(sub(int(formatDateTime(utcnow(), 'yyyy')), 1)), '-01-01'),concat(formatDateTime(utcnow(), 'yyyy'), '-01-01')),'yyyy-MM-dd')

Two changes are made in the expression of StartDate:

• equals() logical function is used instead of == operator to compare the current date with [current year]-01-01 date.
• Concat function concatenates the string values. Thus sub(int(formatDateTime(utcnow(), 'yyyy')), 1) is converted to String using string() function before concatenating it with the value -01-01.

Expression for End date:

The logic for EndDate can be simplified as, If day is less than 10, then end date should be last day of previous month. Otherwise, it should be last day of current month.

@formatdatetime(if(less(dayOfMonth(utcnow()),10),adddays(startOfMonth(utcnow()),-1),adddays(startOfMonth(addToTime(utcnow(),1,'Month')),-1)),'yyyy-MM-dd')

The resulting variable is a date string in the format yyyy-MM-dd, representing either the last day of the current month or the last day of the previous month, depending on the current day of the month.