Date casting query

[Dev1975]

Hello Everyone,

Firstly, apologies if this has been answered already but I can't seem to find the appropriate thread at the moment, hence the new topic.

I'm trying to cast a date value in FM from the current Character format to Date (YYYYMMDD HH:MM:SS) format. I'm using "cast" (datasource is SQL Server) but can only cast to "date" or "time". When casting to the former, it returns the date in "mmm:dd;yyyy" format, which is not what I want. I have tried specifying the pattern in FM but that doesn't work.

At present, my query definition looks like this:

cast([DataItem],date)

Anyone know how I can specify the format to be returned? If I simply leave it as just the data item without the "cast", it returns a full date and time with milliseconds, however I cannot alter this format in ReportStudio in any way.

Any suggestions appreciated!!

[TheCognosDave]

you only use cast on a field that isn't the field type you want.  If it's already a datetime field ... just format it.

do this instead...

format([DataItem],'yyyyMMdd hh:mm:ss')

unless it's not a date field ... then you need to cast before formatting

format(cast([DataItem] as Date),'yyyyMMdd hh:mm:ss')


Good luck !

[Dev1975]

Hi, thanks for your response. I tried your suggestion but got a message stating that "format" was an unrecognized function. Did a bit of Googling and found that I would have to use "convert" instead of "format", so altered the definition but now get the following:

UDA-SQL-0564
[Microsoft SQL Server Native Client 10.1]
Incorrect syntax near the keyword 'as'
(SQLSTATE=42000, SQLERRORCODE=156)

The query is as follows:

convert(cast([DataItem],date),'yyyyMMdd')

I've tried adding "as" before the 'yyyyMMdd' part as well as before the "date" part, however it still isn't working. Any ideas??

[TheCognosDave]

hmmm ... format is a SQL function, but maybe Cognos doesn't like it.

Try this  ... 
1) Add a New SQL object to your Cognos Report
2) Set the Datasource to your SQL Server reporting Database
3) Set the SQL Syntax to Pass through
4) Set the SQL statement to the following:

SELECT CONVERT(VARCHAR(19),sysdatetime(),112) + ' ' + CONVERT(VARCHAR(19),sysdatetime(),108) as RightNow

If that works ... replace the sysdatetime() with your [DataItem] field and add your FROM clause.

Lets see if we can get this by process of manipulation ;-)

Good Luck !

[Dev1975]

Thanks, I'll give that a go and let you know how I get on. Much appreciated! :)

[MFGF]

Ok. Stop. Step away from the keyboard and let's calm down. :) Coding SQL manually in a BI tool is a last-ditch last-resort to-be-avoided-at-all-possible-costs-unless-the-world-is-ending practice, so let's not go down that route as a first course of action.

The first and most important question is whether the item in your database is a date (or datetime) datatype, or whether it's a string. If it's a string, simply use the standard Cognos cast() function in a calculation to convert it to a date

eg cast([your original string item], date)

Once you have done this (or if you don't need to because it is already a date), you don't need any scripted code or sql query or function to format it. Simply select the item and use the data format property to describe how you would like it to be displayed. The only hiccup is that this is not referenced when you test the item from within FM (for some unfathomable reason) but it does apply when the item is being used in a reporting studio or report output.

Cheers!

MF.

[TheCognosDave]

LOL ! ::)