Thursday, May 26, 2011

How to Convert Date into Integer in RPD ?

Converting date(1/1/1998) to integer (19980101) in RPD:

Create a logical calculation in the rpd as below [replace "Sample Sales"."D0 Time"."T00 Calendar Date" with the date column that you want to use in the formula]

CAST( SUBSTRING(CAST("Sample Sales"."D0 Time"."T00 Calendar Date" AS VARCHAR(10)) FROM 1 FOR 4 ) || SUBSTRING(CAST("Sample Sales"."D0 Time"."T00 Calendar Date" AS VARCHAR(10)) FROM 6 FOR 2 ) || SUBSTRING(CAST("Sample Sales"."D0 Time"."T00 Calendar Date" AS VARCHAR(10)) FROM 9 FOR 2 ) AS INT )

Until Next Post ... :)

2 comments:

  1. Hi,

    I have a "Month" columns which has data like "2000 / 01" which is of Varchar Data Type.When i create a presentaiton variable for this column it is doing arithmetic calculation and it is returning 1000 as a result.Since it is a Varchar type i need to convert it to Date data type.Cast function is not working and we dont have To_date function as well.I was struck here.Can u please help me out if u can??.Thanks in Advance

    ReplyDelete
  2. Hi,

    I have a "Month" columns which has data like "2000 / 01" which is of Varchar Data Type.When i create a presentaiton variable for this column it is doing arithmetic calculation and it is returning 1000 as a result.Since it is a Varchar type i need to convert it to Date data type.Cast function is not working and we dont have To_date function as well.I was struck here.Can u please help me out if u can??.Thanks in Advance

    ReplyDelete