Create a variable where the output is the date. Input we give is only the present Year.Date value should be 2nd Sunday of October

Krishnamohan Nadimpalli 406 Reputation points

Hi All

Create a variable where the output is the date. Input we give is only the present Year. Date value should be 2nd Sunday of October
I am trying to create this variable in Azure Data Flows


Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,138 questions
{count} votes

Accepted answer
  1. AnnuKumari-MSFT 32,011 Reputation points Microsoft Employee

    Hi @Krishnamohan Nadimpalli ,

    Thankyou for using Microsoft Q&A platform and thanks for posting your question here.

    As I understand your ask here, you want to fetch the date belonging to second sunday of the year you provide as an input using mapping dataflow in ADF pipelines. Please let me know if that is not the ask here.

    Unfortunately, there is no direct function to achieve this requirement. However, you can use the following dynamic SQL query and either create a stored procedure for it and use stored procedure option in source transformation of mapping dataflow, having source dataset as SQL server. Or, you can directly use this query in look up activity within ADF , pointing the dataset to SQL server.

    DECLARE @month TINYINT ,  
    @year SMALLINT ,  
    @date DATETIME  
    SELECT @month = 10 ,  
    @year = 2022  
    SET @date = CAST( CAST( @month AS VARCHAR ) + '/01/' + CAST( @year AS VARCHAR ) AS DATETIME )  
    SELECT @date + 7+ ( 7 - DATEPART ( weekday , @date ) + 1 ) % 7 as secondSunday  

    Here you can parameterize @month and @year variables when using in stored procedure and pass the values during the runtime.

    Hope this will help. Please let us know if any further queries.


    • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you.
      Original posters help the community find answers faster by identifying the correct answer. Here is how
    • Want a reminder to come back and check responses? Here is how to subscribe to a notification
    • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators
    0 comments No comments

0 additional answers

Sort by: Most helpful