BusinessObjects Board

Map Operation and Datetime

dataservices job design
#1

Environment: SAP DS 14.2.3 on Linux loading SQL Server 2012.

I have a data flow, with Map Operation performing an update to a table, which throws an error:

Timestamp parameters with a scale, must have a scale less than ten and a precision equal to 20 plus the scale. You specified a precision of 19 and a scale of 3.

If I truncate the table and run for the first time, no error is thrown however, if an update is detected an error is thrown on the datetime column.

If I change the column datatype on SQL Server to date, it runs perfectly. I have tried to perform both to_date() and convert, without success. Any ideas?

Decisys :de: (BOB member since 2004-05-21)

#2

Cast function?

Mak 1 :uk: (BOB member since 2005-01-06)

#3

Sorry, I meant that I tried using the cast function and not convert function.

Decisys :de: (BOB member since 2004-05-21)

#4

Can you post the syntax you were using?

Mak 1 :uk: (BOB member since 2005-01-06)

#5

Sure:
cast([date column], ‘timestamp’)
cast(date column], ‘datetime’)
to_date(date column], ‘yyyy.dd.mm hh:mm:ss’)
to_date(date column], ‘yyyy.dd.mm hh:mm:ss.[n*]’)
etc…

Decisys :de: (BOB member since 2004-05-21)

#6

Syntax for CAST:
CAST ( expression AS data_type [ ( length ) ] )[/code]

https://msdn.microsoft.com/en-us/library/ms187928.aspx

You could always look at the convert funtion too.

Mak 1 :uk: (BOB member since 2005-01-06)

#7

I am not sure that the cast () syntax is the same for SP DS and SQL Server.

I still get the same error when:

to_date(cast([date column], ‘varchar(20)’),‘datetime’) and cast([date column], ‘varchar(20)’)

Decisys :de: (BOB member since 2004-05-21)

#8

I have found a workaround, changing the the datatype to varchar but I would like to keep this post open as I do feel this is a bug in SAP DS.

Decisys :de: (BOB member since 2004-05-21)

#9

Hi There,

I think this is a right thread for my issue, I have date in source as varchar(1024) which when applied with to_date(‘sourcedate’,'YYYY-MM-DD HH24:MI:SS) conversion it converts Target date of Type ‘Datetime’ to :
yyyy.mm.dd hh24:mi:ss.nnnnnnnnn (Timestamp format)

I don’t want it to get converted to datetime and not Timestamp, please can you help me with this issue ?

Thanks in Advance!!!

BOBJFan (BOB member since 2011-09-24)