I have a Data Lake Store account. I have a directory full of files containing data in JSON format, including some string values that contain times in ISO 8601 format, to wit: { "reading_time": "2008-09-15T15:53:00.91077" }
Now when I create a Pipeline with a Data Factory that uses these JSON files as an input dataset, it sees the value of reading_time
in a typical US format: "9/15/2008 3:53:00 PM"
. Specifically, I get this message when I try to populate a DateTime
field in the output dataset:
Column 'reports.reading_time' contains an invalid value '9/15/2008 3:53:00 PM'. Cannot convert '9/15/2008 3:53:00 PM' to type 'DateTime'
I thought hey, what if I tell my input dataset to specifically expect an ISO input date? So I tried this in my pipeline specification:
"datasets": [
{
"name": "ImprovedInputDataset",
"properties": {
"structure": [
{
"name": "reports.reading_time",
"type": "Datetime",
"format": "ISO"
}
]
}
}
}
]
I was pretty impressed with myself for getting a slightly different error message (see "with format 'ISO'" at end):
Column 'reports.reading_time' contains an invalid value '9/15/2008 3:53:00 PM'. Cannot convert '9/15/2008 3:53:00 PM' to type 'DateTime' with format 'ISO'
Long story short, it seems as though something is noticing the ISO date format in my original input and doing me the dubious "favor" of converting it to a US-style date string before my pipeline gets to see it. I can't find anything in the Azure documentation online that explains exactly what happens to my input dataset before my Pipeline spec executes though.
I would appreciate if someone would either a) explain to me what it is that's converting my ISO date/time string to a US type date/time string and how to correct it; or b) point me to the documentation on the "preprocessing" that must be happening inside the Data Factory before my Pipeline spec is run.
varchar(256)
for that parameter, but ADFv2 formats the string into a specific datetime locale first. This doesn't happen when invoking a stored procedure directly. I plan to file a bug report. – bdforbesdatetime
type. However, it actually seems to work perfectly, so I just pass the datetime string through, with the stored procedure expecting it asdatetime
. – bdforbes