Query about handling SQL Server TIME fields

AETLP v6.3.8.3 x32

Hi. We have data from an external source that has date and time in 2 separate fields. We remapping this like for like into an SQL Server table where we also have a TIME field that SS defines as TIME(7).

The source data is formatted as HH:MM:SS and as such I imagined I could just pass it through.

But I was getting SQL errors (as per the screenshot). I suspect it was getting the default Date/time format from the READER properties which I can't override as there are datetime stamps in the file as well.

So, I then decided to try putting in a REFORMAT DATE action with a format of HH:NN:SS into the transformer just to see if I could force it through but again this resulted in the same message. So now I am wondering if I need to make up a dogey date and appended it to the front of the time and then reformat on all of that. But this to me seems a lot of work and also there is room for the reformat to fail etc etc.

We also need to cater for the fact that these times may also be NULL which is also valid. BUT the reformat DATE function no matter what the format is will not handle NULLS without throwing an exception.

In this case NULL times are valid BUT if there is an invalid TIME it needs to be aborted (not replaced with another value).

II have raised the latter issue in regard to NULL dates as well but as yet have not figured out a workaround and the one that was suggested is not valid for TIME reformatting.

Any ideas? Quite frustrating as this is a relatively straight forward transformation except for this.

thanks in advance

You are making things far too complicated.

The only function you ever need to load data into date or time fields is Date Format

www.etl-tools.com/wiki/advanced-etl-proc...ate-and-time-fields/

I run my own test and it worked as it should be

Thanks Mike and appreciate your comment. Maybe I wasn't terribly clear in my query. The real issue is that the SOURCE Time field is just a time formatted as HH:MM:SSZ so a sample time would be 05:03:01Z

I strip the 'Z' at the moment leaving 05:03:01

Without ANY reformatting just passing this into the outfield result with is defined in SQL Server as a TIME only field. My first attempt resulted in the "Can't apply format' error for each row that had a time formatted in this way. In this particular file the time is all the same for about 50% of the rows and the other 50% have a NULL time.

BUT... Ignoring the NULLs for the moment I thought that I would just use a DATE REFORMAT to basically do what you are saying to overcome the error message. Except that in my case - there is no DATE portion of the Source Field. It is basically a text field formatted like a TIME (with no DATE). Therefore, there is no DATE in the source to ignore anyway.

To be honest I am not sure why it is getting rejected. From what I can see it hasn't even reached the SQL engine it seems to be in the transformer other wise I would imagine I would see an SQL Engine based error.

In order to load data into target database's Date or time fields, it should be converted into 'YYYY-MM-DD HH:NN:SS.FFF' format.

I can see from your screenshot that the format is incorrect

Hi Mike

Sorry for the delay in a response. I had a few things on the go.

Perhaps I am still not quite clear in my explanation. Forget the screen shots I have taken as they are probably being taken at the wrong point and are just confusing things.

The issue for me (and there is another similar ticket about this) is that I like the option for the REFORMAT DATE action to ABORT if the date OR time reformatting fails. HOWEVER, if the inbound DATE or TIME is NULL then this is VALID for us and the NULL should just be passed through to the output field and then pushed as NULL to the database table which allows NULLS in this particular field. The problem at the moment is that I can't have both options.

If the inbound date/time is NOT NULL but contains an invalid value I can ABORT or change the output to NULL. If I do the latter then I lose the fact that we actually received BAD data or have a formatting issue, or they changed the shape of the inbound data. If the inbound date/time is NULL from the source then we don't need to worry about trying to reformat it - we just need it to get sent through to the output field. But that is not possible at the moment because a NULL value in the REFORMAT action will raise an error which I can either ABORT ON, change value or output null - I can't just ignore it.

So it is kind of like I need a condition in the Reformat Date Action separate from the ON ERROR handler so that if the inbound value is NULL then just simply ignore the reformatting and pass Null as the output value. If however, there is a value then it needs to attempt the reformat and then run through the ON ERROR section if necessary OR pass the reformatted value through.

We have the same issue for DATE ONLY, TIME ONLY and DATETIME combinations where we still want to ABORT the transformation if an invalid date is found but pass through NULL without any formatting if NULL is supplied from the READER.



Did that make sense?


thanks

Thank you for the clarification.

We will have a look at it for you