on 09-27-2019 9:22 PM
Hi,
I'm using SAP data services to extract data from some Plant Maintenance tables into an SQL database. The extract fails on some fields with a time data type even though the SQL field is also data type time(7). It seems to work OK when there's an actual time value in the PM table field (i..e. 07:00:00 000000000) but fails if the time value is 24:00:00 000000000 or 00:00:00 000000000.
Why is this? Cast(time_field, 'time') doesn't work either. to_char(time_field, 'H24:mm:ss') as varchar(24) field works but I prefer not to use that since the back end SQL database may have issues calculating time differences. Is there any other function in SAP data services that I can use to force a SAP time data type into an SQL time data type?
Thanks
Jeff
Hi Werner,
Yes, used ifthenelse() to edit the offending date and that worked fine. Thanks for the help.
Jeff
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Werner,
Thanks for the response.
SAP data services is defaulting the field to the time data type and I have also defined the backend SQL field as a time(7) data type.
The field GLUZP in table AFKO indeed has value 24:00:00 000000000 in the field. This is on our dev server.
Have had other time fields in other SAP tables fail on the time data type and used to_char to get the extract to load into the backend SQL database.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
'24:00' is not a valid time. Your clock does not show that value either. It shows 23:59 and 00:00.
I would be interested in the exact datatype being used according to SQL Server. Because per my knowledge it does not support 24:00 either.
see https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-2017
So either this is a string data type, or I am wrong, or there is some math going on that causes the rounding to 24:00. Depending on the case, we need to act differently, e.g. using a ifthenelse() to capture this time value.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.