This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(121.6, 92%, 21%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EPV%3C/text%3E%3C/svg%3E)
Hi
In my source table in SQLDB I have a field, PREANC_DTS datetime2(7)
Example : 2022-03-16 11:56:12.0000000
I want to split the datetime field in a date and time field: PREANC_DT (datatype date) and PREANC_TM (datatype time(7)).
In ADF expression builder this works for PREANC_DT:
iif(isNull(ROK_SLBD_PREANC_DTS),toDate('0001-01-01'),toDate(toString(ROK_SLBD_PREANC_DTS, 'yyyy-MM-dd')))
For PREANC_TM I can get it working , although I tried many things. As you can see I het the time portion, but the output field PREANC_TM becomes a String.
Throughout the data flow the fields keep the right time value, but in the end, in the sink, all those fields become NULL. The pipeline fails and says: PREANC_TM can't be NULL.
I think it happens because the Sink expects a time(7) field and not a string. How to solve and why is there not just a simple toTime function or whatever? :(
Hope someone can help. Thnx a lot
Ron
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(256, 32%, 34%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMT%3C/text%3E%3C/svg%3E)
I cannot easily test this but consider using toDate on the base column to get the date portion and then toTimestamp on the base column to get the timestamp. You shouldn't need to parse the string yourself.
Hi,
as said toDate works for PREANC_DT.
toTimestamp always gives NULL value for PREANC_TM.
Post the code you're using toTimestamp on. I would wager your format specifier is incorrect.
iif(isNull(ROK_SLBD_PREANC_DTS),toTimestamp('00:00:00','hh:mm:ss'),toTimestamp(toString(ROK_SLBD_PREANC_DTS),'hh:mm:ss:'))
Be aware ROK_SLBD_PREANC_DTS is a timestamp field
Beneath you see a AlterRow and Sink activity
In the AlterRow datatype is String and values are filled.
In the Sink, immediately following the AlterRow, datatype becomes Time/DateTime and values get NULL
In the target table it is a time(7) field. Looks like ADF is unable to handle that.
Please use the Comment link to respond to answers so we can more easily understand who you're responding to. Thanks.
Be aware ROK_SLBD_PREANC_DTS is a timestamp field
In your original post you said ROK_SLBD_PREANC_DTS is a DateTime2 column.
Your expression isn't correct. If the column is NULL you're converting the column to midnight. Otherwise you're converting the column to a string and then trying to parse it as just a timestamp. I think you should just try doing this: iif(isNull(ROK_SLBD_PREANC_DTS),toTimestamp('00:00:00','hh:mm:ss'),toTimestamp(toString(ROK_SLBD_PREANC_DTS))). If that doesn't work then add the format of the string as the second parameter. Right now you're specifying just a time but the column would include the date and hence it is failing.
Alternatively adjust your toString to specify the format you want and then the use the same for toTimetamp. Perhaps something like this: toTimestamp(ROK_SLBD_PREANC_DTS, 'hh:mm:ss.SSS'), 'hh:mm:ss.SSS') That forces the column to a well defined format and then tells toTimestamp to parse that same format.
Thnx, @Michael Taylor , but the issue remains that in the sink it becomes NULL.
Looking at https://spark.apache.org/docs/latest/sql-ref-datatypes.html it seems Spark/ADF is not supporting the Time datatype.
So, I think this problem is somehow unsolvable
I think we have to re-evaluate and not separate Date and Time but just make a DateTime field.
Thnx anyway for thinking with me