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
Quote from: lahdeb on 02 Jun 2017 06:48:50 AM
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.
_add_months(
cast (
substring ( [Month/Year], 4, 4 )
|| '-' ||
substring ( [Month/Year], 1, 2 )
|| '-01'
, date )
, 1 )
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.
Quote from: hespora on 02 Jun 2017 07:32:58 AM
/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.
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:
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:
cast(right([Month/Year], 4) + '-' + left([Month/Year], 2) + '-01', date)
Thank you for your replies , I m using Teradata so casting the date into universal format is not necessary
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
All I had to do is to change the data format of my date from the properties panel :D