cancel
Showing results for 
Search instead for 
Did you mean: 

time data conversion

0 Kudos
661

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

Accepted Solutions (0)

Answers (3)

Answers (3)

0 Kudos

Hi Werner,

Yes, used ifthenelse() to edit the offending date and that worked fine. Thanks for the help.

Jeff

0 Kudos

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.

werner_daehn
Active Contributor
0 Kudos

ifthenelse() it is then. Or keep the value as string. Anyway, you are good to go now, aren't you?

PS:

In SAP tables you can find the date 00000000 also, which means <null>. But from a pure techncial point of view, year 0 is fine, month and day zero is not.

werner_daehn
Active Contributor
0 Kudos

'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.