ReportStudio CAST functions

[chinnaseenu]

Hai,

I've SQL Server Column 'DueDate' that is a string data type but it stores a date value
as 'yyyymmdd'(eg:20050816) i want to convert it into integer ,when i use SQLServer function
cast_integer([DueDate]) i'm getting the error that the function does not exist.

please any kind of help would be great i'm stuck with that for couple of days.

THANX alot

 

[KingCrab]

select cast('20050816' as datetime)

 

[KingCrab]

sorry hit submit a bit quick

cast_timestamp('20050816') will give you a real date
cast_integer('20050816') will give you an integer

What I was saying above is that cognos actually does a
convert(int,'20050816') in the SQL itself
not a cast..This should work. I would look at the generated SQL and see if you can figure out what it is actually doing.

 

[chinnaseenu]

Hai thanx for the reply,
but it's working if i give explivitly like'20050816'
and it's not working if i send a column from sqlserver data base
Cast_integer([duedate])
i'm getting that the function does not exist.

QE-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-228'.

UDA-SQL-0219 The function "cast_integer" is not available as an external, database, or built-in function.

 

[KingCrab]

Where are you adding this? If you are adding it to an actual SQL statement (tab sql or something) then you must use the proper sql. Cast_integer is not a command that work natively with sql server. You need to use convert

 

[chinnaseenu]

i really thank you for your patience and helping me.

actually i'm getting that column [DUEDATE] from SQLServer into a tabular model
there i dragged 'Dataitem' and in the expression of that i wrote "cast_integer([DueDate])".


How should i use convert in reportnet does it work for any database? could you please give me the syntax?