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

Post Reply
Ptec
Posts: 15
Joined: Mon Mar 14, 2016 9:14 am
OLAP Product: TM1
Version: 10.2.2.0
Excel Version: 2013

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

Post by Ptec »

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!
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

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

Post by Wim Gielis »

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.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Ptec
Posts: 15
Joined: Mon Mar 14, 2016 9:14 am
OLAP Product: TM1
Version: 10.2.2.0
Excel Version: 2013

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

Post by Ptec »

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!
Post Reply