Converting a Date Format to YYYYMM in Cognos Report Studio

sql datetime cognos cognos-8 cognos-10
Excited_to_learn picture Excited_to_learn · May 29, 2014 · Viewed 20k times · Source

I have a date column in a format 12 May, 2014 I want to convert that into yyyymm/ 201405 format. i tried multiple options like 

extract(year, sys_date)*100 + extract(month, sys_date))*100
cast(extract( year, sys_date), varchar(4)) + cast(extract( month, sys_date), varchar(2))

This one works but returns me in YYY,YMM format. 

CAST(to_char(sys_date, 'YYYYMM'), INT )

For the above two, it gives an error:

 An error occurred while performing operation 'sqlOpenResult' status='-28'

Can some one please guide. Thank you in advance.

Answer

Excited_to_learn picture Excited_to_learn · May 29, 2014

I got what I was looking for. This is what I did:

translate(CAST(to_char(sys_date, 'YYYYMM'), varchar(6) ), ',' , ' ')

I am sure there would be a better way to get the result but for now I would use this SQL as it gives me what I want :)

Related questions

How to return only the Date from a SQL Server DateTime datatype

SELECT GETDATE() Returns: 2008-09-22 15:24:13.790 I want that date part without the time part: 2008-09-22 00:00:00.000 How can I get that?

Read More
SQL query to select dates between two dates

I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query. select Date,TotalAllowance from Calculation where EmployeeId=1 and Date …

Read More
How to convert DateTime to VarChar

I am working on a query in Sql Server 2005 where I need to convert a value in DateTime variable into a varchar variable in yyyy-mm-dd format (without time part). How do I do that?

Read More