Topic
  • 2 replies
  • Latest Post - ‏2009-07-15T18:36:04Z by hauge9
BhagatS
BhagatS
150 Posts

Pinned topic using CAST in Framework Manager

‏2009-07-15T16:00:01Z |
I'm wanting to link two tables based on a date field.
Table 1 has the column stored as a datetime data type
Table 2 has the column stored as a varchar data type.

Now in F.M - in the calculation part, I try and cast the column from table 1 as varchar (this produces an error, but works with char, but produces rubbish data.

When casting column 2 as a date is says arithmatic overflow.

Any any ideas how to cast either one so they are the same.

Many thanks.
Updated on 2009-07-15T18:36:04Z at 2009-07-15T18:36:04Z by hauge9
  • dtremain
    dtremain
    455 Posts

    Re: using CAST in Framework Manager

    ‏2009-07-15T17:43:39Z  
    Use the built-in date / time formatting / conversion functions from the native database. CAST is not always a reliable method to shift datetime values to / from other data types. For example, if I'm not mistaken, a SQL Server datetime field is stored as the number of sub-second time chunks since a particular start date, so converting it to char will simply give you a character representation of that number. Most dbms have specialized functions to format a datetime as string / varchar or to convert a string to a datetime data type. Even if the native db function is not listed in the functions list in Cognos, if it exists in the db, it will work from Cognos.
  • hauge9
    hauge9
    277 Posts

    Re: using CAST in Framework Manager

    ‏2009-07-15T18:36:04Z  
    What type of database are you going at?

    If it was Oracle I would do a to_char() on the date field and make it look like the text field.