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?
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
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
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:
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:
( 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.
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 !!
Doesn't something like this work?
extract(year, [Date])*10000 + extract(month, [Date])*100 + extract(day, [Date])
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 ) )
Quote from: David_Efr on 31 Aug 2020 03:14:08 AM
Doesn't something like this work?
extract(year, [Date])*10000 + extract(month, [Date])*100 + extract(day, [Date])
This would be the approach I would take as well
Many thanks - The extract approached worked perfectly !!
cast ( convert(varchar, getdate(), 112) as int)