Converting characters to date

I have LOADDATE and PSTGDATE both in character format. When I try to change them to date using ToDate() in Reporter it gives me an #ERROR error. I need to find the number of days between these two dates.

I tried setting them to Date format in the designer; but what happened was all dates were set to 01/01/1900. I also tried usuingto_date() in designer. Dint work. Again I got 1/0/1900.

How do I find the number ofdays between these two characters?

LOADDATE looks like 20020418 (currently set as Character in the Universe)
PSTGDATE looks like 20040623 (currently set as Character in the universe)

Thanks.


tendulkar :india: (BOB member since 2004-03-15)

If you are in Oracle, you could use to_date(column_name,“YYYYMMDD”) if memory serves me rightly.

Thanks Mark !!! That worked. I tried to_date(LOADDATE,“YYYY/MM/DD”) and this dint work. But to_date(LOADDATE,“YYYYMMDD”) worked.

Thanks again.

Chandru


tendulkar :india: (BOB member since 2004-03-15)

This solution works in designer but not in reporter. I have a similar problem and whilst searching on here I found this post.

I’m importing data from a simple CSV file into BO. I want to convert one of the columns to a date field. The date in the file is in the format 20040422. BO thinks this is a number so imports into the report as such which is fine. I’ve tried to create a variable on this column to convert it to a date but keep getting errors.

I’ve tried ToDate(,“YYYYMMDD”) and I get an incorrect data type error. When importing, BO thinks the type of the object is numeric.

I’m really scratching my head at this as I’m sure it is really simple. I’m convinced I had this working yesterday but can’t for the life in me remember how I did it :shock: !


jmmorton :uk: (BOB member since 2004-07-05)

I’ve tried ToDate(,“YYYYMMDD”) and I get an incorrect
data type error. When importing, BO thinks the type of the object is
numeric.

You read “incorrect data type error” because is a number. Try to use FormatNumber(, “0”) instead of , as follows:

ToDate(FormatNumber(, “0”), “YYYYMMDD”)

Please, let us know! :wink:


Christian Konrads :it: (BOB member since 2004-07-21)

Thanks Christian, exactly right.

It was the number to character string bit that I was missing. Glaringly obvious, but missing!!


jmmorton :uk: (BOB member since 2004-07-05)