How to use built in date-time functions in Azure Data Factory? RRS feed

  • Question

  • Hello,

    I am trying to create a pipeline that passes some extended properties to my Hive script in Azure Data Factory.

    I have successfully been able to pass the SliceStart Day, Hour, Year, etc. but I am now trying to do something more complex.

    I want to tell my Hive script yesterday's date. I know there are some built-in dateTime functions in ADF. In the documentation (https://msdn.microsoft.com/en-us/library/azure/dn835056.aspx) however, they don't give examples of syntax for using them. My approach has not worked so far... here is what I'm trying:

        "name": "Last24Hours_Exceptions",
        "properties": {
            "description": "MDS Aggregation Exceptions Pipeline",
            "activities": [
                    "type": "HDInsightActivity",
                    "transformation": {
                        "scriptPath": "hddefault\\datafactory\\queries\\....hql",
                        "scriptLinkedService": "HdInsightStorageLinkedService",
                        "type": "Hive",
                        "extendedProperties": {
                            "StorageName": "...",
                            "StorageContainer": "...",
                            "Year": "$$Text.Format('{0:yyyy}',SliceStart)",
                            "Month": "$$Text.Format('{0:%M}',SliceStart)",
                            "Day": "$$Text.Format('{0:%d}',SliceStart)",
                            "Hour": "$$Text.Format('{0:%H}',SliceStart)",
                            "Minute": "$$Text.Format('{0:%m}',SliceStart)",
    "Yesterday": "$$Text.Format('{0:%d}', AddDays(SliceStart, -1))"
                    "inputs": [.....

    All the extended properties work except "Yesterday". Any suggestions on syntax?

    • Edited by Danielle F Friday, May 8, 2015 9:26 PM
    Friday, May 8, 2015 9:23 PM


  • Figured it out - I was missing the 'category' or namespace before the function:

                            "YesterdayYear": "$$Text.Format('{0:yyyy}',Date.AddDays(SliceStart, -1))",
                            "YesterdayMonth": "$$Text.Format('{0:%M}',Date.AddDays(SliceStart, -1))",
                            "YesterdayDay": "$$Text.Format('{0:%d}',Date.AddDays(SliceStart, -1))"

    Tuesday, May 12, 2015 12:19 AM