casting varchar to date

[lahdeb]

Hi community

Can you please help me to cast a varchar format to a date format ?

I have a data element which contain a varchar (month/year ) example : 05/2017 , I want cognos to read it as a date so I could add days months , my request is to obtain 01/06/2017 from my date (05/2017)

so I need to do something like : add months (cast ('01'+'/'+ 'name of data element' , date))

Thank you for your help

[Lynn]

Hi community

Can you please help me to cast a varchar format to a date format ?

I have a data element which contain a varchar (month/year ) example : 05/2017 , I want cognos to read it as a date so I could add days months , my request is to obtain 01/06/2017 from my date (05/2017)

so I need to do something like : add months (cast ('01'+'/'+ 'name of data element' , date))

Thank you for your help

The below code uses substring with concatenation to convert your 05/2017 to 2017-05-01. That expression is then wrapped in a cast function to convert it to a date data type. The outermost function adds one month to the date.

This is applicable for a relational data source, not dimensional. Hope it helps solve your problem.

Code Select Expand


_add_months(
    cast (
        substring ( [Month/Year], 4, 4 )
        || '-' ||
        substring ( [Month/Year], 1, 2 )
        || '-01'
    , date )
, 1 )


[hespora]

you'll want to look into the substring() and _make_timestamp() functions; together with cast() and _add_months

from inside to outside:
substring to take apart the origin string
cast to turn substrings into integers
_make_timestamp to build the date (with day no. 1 plus month and year from your string)
_add_months to add one month


/edit: or do what Lynn said, if that works for you. On my system, I cannot cast into Dates; I have to use _make_timestamp. But if casting into a date works for you, that saves you one level of complexity in the definition.

[Lynn]

/edit: or do what Lynn said, if that works for you. On my system, I cannot cast into Dates; I have to use _make_timestamp. But if casting into a date works for you, that saves you one level of complexity in the definition.

Ah, very good point, hespora! There may well be different approaches for different databases. I am on DB2. Some databases may have only date/time data types rather than date as well as date/time data types.

[dougp]

Lynn is absolutely right to convert the date string into the standard, universal format (yyyy-mm-dd) to avoid inconsistencies between locales, then casts it to a date.

Stating what DBMS you use would be helpful.

I'm using MS SQL Server.  Here's SQL code that demonstrates my logic:

Code Select Expand


declare @a varchar(24)
set @a = '05/2017'

select @a
, {d '2017-05-01'}
, cast(right(@a, 4) + '-' + left(@a, 2) + '-01' as date)


So my data item expression would be:

Code Select Expand


cast(right([Month/Year], 4) + '-' + left([Month/Year], 2) + '-01', date)


[lahdeb]

Thank you for your replies , I m using Teradata so casting the date into universal format is not necessary

[lahdeb]

Hi all

I casted the varchar into date but cognos gave me the date with caracter format :
example  , when I did my
: cast (substr([month/year];4;7) + '-' + substr([month/year];1;2)+'-01'); date) ,

knowing that my [montth/year]= 03/2017 , cognos gave me 01 Mars 17 , but my need is to have 01/03/2017

Thank you

[lahdeb]

All I had to do is to change the data format of my date from the properties panel :D