Creating date from non-date data

[camjam980]

I have an int field in the form of YYYYMMDD and I want to create a date from it in cognos. I've tried to_date([Date], 'YYYYMMDD') and to_char([Date], 'YYYYMMDD') and can't find another function that would accept components to create a date (eg: create_date(YYYY, MM, DD)).

Any advice?

[schrotty]

Hi,

To_char and to_date are nit realy cognos built in functions.
In cognos you use cast.

To convert your integer value into a date-value you need to perform the folloeing steps:
1. Convrt your integer to char: cast([Date] as varchar(10))
2. Split the char into year, month and day parts: substring ( cast([Date] as varchar(10)) , 1 , 4) ...
3. Cognos can convert a string to date if the string has this format: YYYY-MM-DD so you must concat your dateparts with '-'
4. Convert the new string to a date

Code Select Expand


cast ( ( substring ( cast([Date] as varchar(10)) , 1 , 4) || '-' || substring ( cast([Date] as varchar(10)) , 5 , 2) || '-' || substring ( cast([Date] as varchar(10)) , 7 , 2) ) as date )


I'm not recommend this solution becaus it isn't the best performace. Try to extend your data source with a real date field.

Schrotty

[Lynn]

Also bear in mind that if you convert your integer date to a real date and use it in a filter it may perform really terribly. For example, if you want to use current_date compared to dates in your data source.

There won't be any index in your database on the expression schrotty gave you so trying to do this may work properly and give the right result but the performance may be a hindrance:

Code Select Expand


current_date =
cast ( ( substring ( cast([Date] as varchar(10)) , 1 , 4) || '-' || substring ( cast([Date] as varchar(10)) , 5 , 2) || '-' || substring ( cast([Date] as varchar(10)) , 7 , 2) ) as date )


Instead flip things around and transform current_date into an integer for purposes of the comparison:

Code Select Expand


( extract (year, current_date) * 10000) + ( extract (month, current_date) * 100 ) + ( extract (day, current_date) )
=
[Date]


And I agree it would be better if your data source could be enhanced to include the date in the proper data type. I am assuming a relational source although you didn't specify.

[srmoure]

I have the opposite problem in a data module. I have a date field yyyy-mm-dd and I would like to convert it to an integer yyyymmdd to use as date key. This should be easy but I cannot get it to work. Closest I got was using YEAR, MONTH and DAY functions, with the problem of converting 2018-02-07 into 201827 when I need 20180207.
Thanks !!

[David_Efr]

Doesn't something like this work?

Code Select Expand

extract(year, [Date])*10000 + extract(month, [Date])*100 + extract(day, [Date])

[srmoure]

INT ( CAST ( EXTRACT ( YEAR, Posting_Date_in_the_Document ), VARCHAR ( 4 ) ) ||
CASE
    WHEN EXTRACT ( MONTH, Posting_Date_in_the_Document ) < 10
    THEN '0' || CAST ( EXTRACT ( MONTH, Posting_Date_in_the_Document ), VARCHAR ( 1
) ) ELSE CAST ( EXTRACT ( MONTH, Posting_Date_in_the_Document ), VARCHAR ( 2 ) )
END ||
CASE
   WHEN EXTRACT ( DAY, Posting_Date_in_the_Document ) < 10
    THEN '0' || CAST ( EXTRACT ( DAY, Posting_Date_in_the_Document ), VARCHAR ( 1

) ) ELSE CAST ( EXTRACT ( DAY, Posting_Date_in_the_Document ), VARCHAR ( 2 ) )

[BigChris]

Doesn't something like this work?

Code Select Expand

extract(year, [Date])*10000 + extract(month, [Date])*100 + extract(day, [Date])

This would be the approach I would take as well

[srmoure]

Many thanks - The extract approached worked perfectly !!

[Cape Cod Gunny]

cast ( convert(varchar, getdate(), 112) as int)