TM1 Forum

Posted: **Mon Jul 11, 2016 7:10 am**

Hey there,

I'm struggling with following TI Process:
We want to push data from an cube view to an SQL Table - everything works fine except the "Timestamp" from Tm1 to SQL datetime field.

Code - Im generating an Timestamp (String):

Code: Select all

vNow=NOW();
vTimeStamp=TimSt (vNow, '\Y\m\d\h\i\s\');

So as far is I know I would have to convert that string into SQL datetime format with an convert statement like:

Code: Select all

Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

but I dont know how to do it?
Here's my code in the Data Tab - pushing several Variables to the SQL Table except my timestamp:

Code: Select all

odbcoutput('sample_go_DWH', 'INSERT INTO cognos10_gosalesdw.dbo.TM1_FACT_PLAN 
(ACCOUNT_ID,
COST_CENTER_RP_ID,
AMOUNT_PLAN,
AMOUNT_ESTIM,
YEAR_EFFECTIVE,
TARGET_QUALIFICATION,
POPULATION_DATE) 

VALUES ( '''|vAccID|''',
'''|vCostcRP|''',
'''|NumberToStringEx(vPlan,'0.#######','.','')  |''',
'''|NumberToStringEx(vHore,'0.#######','.','')  |''',
'''|vYear|''',
'''|vTarget_Qual|''',
'''|vTimestamp|''')');

Could someone give me a hint which SQL Statement in which format I would have to use instead of inserting the string Variable "vTimestamp" on the End?

THANKS!

Posted: **Mon Jul 11, 2016 7:23 am**

Here is a simple example JUST to show you the way to go, NOT tested at all:

Code: Select all

ODBCOUTPUT('sample_go_DWH', 'INSERT INTO cognos10_gosalesdw.dbo.TM1_FACT_PLAN 
(ACCOUNT_ID,
COST_CENTER_RP_ID,
AMOUNT_PLAN,
AMOUNT_ESTIM,
YEAR_EFFECTIVE,
TARGET_QUALIFICATION,
POPULATION_DATE) 

VALUES ( '''|vAccID|''',
'''|vCostcRP|''',
'''|NumberToStringEx(vPlan,'0.#######','.','')  |''',
'''|NumberToStringEx(vHore,'0.#######','.','')  |''',
'''|vYear|''',
'''|vTarget_Qual|''',
'''|'CONVERT('|vTimestamp|')'')');

See the last line.

Posted: **Mon Jul 11, 2016 9:47 am**

Ive found the solution!

For putting the datetime format into an SQL Table it has to be exact: "YYYY-MM-DD hh:mm:ss“ so as string: „2016-07-11 14:30:23“

So far i changed my timestamp to:

Code: Select all

vNow=NOW();
vTimeStamp=TimSt (vNow, '\Y-\m-\d \h:\i:\s\');

I changed my "Insert" script on the End to:

Code: Select all

'''|vTimeStamp|''',

Thanks anyways for your Tip, maybe someone else finds this helpful!

TM1 Forum

Pushing Data from Cube View to ODBC SQL - with custom SQL Statement (Convert,Cast?)

Pushing Data from Cube View to ODBC SQL - with custom SQL Statement (Convert,Cast?)

Re: Pushing Data from Cube View to ODBC SQL - with custom SQL Statement (Convert,Cast?)

Re: Pushing Data from Cube View to ODBC SQL - with custom SQL Statement (Convert,Cast?)