Topic
3 replies Latest Post - ‏2013-04-05T06:15:16Z by robbiii
robbiii
robbiii
7 Posts
ACCEPTED ANSWER

Pinned topic Create Timestamp from String

‏2013-04-04T15:19:37Z |
Hi

I use Tivoli Common Reporting 2.1 with Cognos 8.4.

My Problem is, that I have a typical ITM timestamp 1130331143559000 and now I would like to make a timestamp.
I found a macro called timestampMask which, when I use this example, gaves me the correct timestamp


( #timestampMask (  
'2013-03-31 14:35:59.000-01:00', 
'yyyy-mm-dd hh:mm:ss' )# )


Now I tried to work with the substring function to create the desired string for the timestampMask macro but without success


( #timestampMask (  
'20' || substring( [TDW].[SERVICE].[MAX_WRITETIME] from 2 

for 2) || 
'-' || substring( [TDW].[SERVICE].[MAX_WRITETIME] from 4 

for 2) || 
'-' || substring( [TDW].[SERVICE].[MAX_WRITETIME] from 6 

for 2) || 
' ' || substring( [TDW].[SERVICE].[MAX_WRITETIME] from 8 

for 2) || 
':' || substring( [TDW].[SERVICE].[MAX_WRITETIME] from 10 

for 2) || 
':' || substring( [TDW].[SERVICE].[MAX_WRITETIME] from 12 

for 2) || 
'.000-01:00', 
'yyyy-mm-dd hh:mm:ss' )# )


Does someone have a idea whats going wrong with this or is there a better way to convert a string to a combined date time field?

Another thing is, can I add 10 minutes to this combined date time field?

Thanks for your answers
Rob
Updated on 2013-04-05T06:15:16Z at 2013-04-05T06:15:16Z by robbiii
  • DanielWagemann
    DanielWagemann
    411 Posts
    ACCEPTED ANSWER

    Re: Create Timestamp from String

    ‏2013-04-04T17:20:41Z  in response to robbiii
    I don't think the macro approach is going to work for you. The reason this is failing for you is that there is no substring function available within the marco expressions. In turn there is not timestampmask function available via the regular expression.

    Best thing to do would be would be to use the substring approach to build up the string into the correct format, then use the cast function to cast it from string to timestamp.
    • robbiii
      robbiii
      7 Posts
      ACCEPTED ANSWER

      Re: Create Timestamp from String

      ‏2013-04-05T05:36:57Z  in response to DanielWagemann
      Could you please show me an example of the correct syntax of cast ( string, TIMESTAMP )?

      Thanks
      Rob
      • robbiii
        robbiii
        7 Posts
        ACCEPTED ANSWER

        Re: Create Timestamp from String

        ‏2013-04-05T06:15:16Z  in response to robbiii
        I found a solution for my problem

        First I have created a data item with the string definition

        
        '2013-04-05 08:14:59.000'
        


        Second I cast the string to a timestamp and add 10 minutes to the timestamp

        
        cast ( [timestamp_string], timestamp ) + 20 minute
        


        That was it!
        Hurray!