Date Time Fields

[sikle]

Greetings (1st Post),

I have a report field that is DateTime such as "05/07/2002 12:00AM".

If I list this as a date field (General,yyyymmdd) in the Report, when I move to the Table I get a Null value. Assuming it can't parse this format.

I want to export the field as "yyyymmdd".

So, I'm forced to list it as a character datafield. I can split the Date from the Time with LSplit, but can't figure out a way to convert the resulting character field to a date field.

Any ideas on how to deal with DateTime fields ?

Thanks,
CPK

[Tom Whiteside]

Hi, CPK!

I work with Oracle Financials 11i, and run into difficult date situations frequently.

If your split-off date character field is named, e.g., "Txt_Date", then try using the character-to-date function:

Ctod(Txt_Date)

The default return format for this function is "yyyymmdd", which is what you want. If you need another date format returned, you can use any of the following three (the " " are required):

Ctod(Txt_Date,"m/d/y")
Ctod(Txt_Date,"d/m/y")
Ctod(Txt_Date,"y/m/d")

If this doesn't work for you, please feel free to send me a private message, and we can hash this out in more detail, without using up bulletin board room.

Good luck! ;)

Tom Whiteside
whitesidetom@yahoo.com

[kkennedy]

I had a similar problem with a date field that was missing the century. I mapped the month, day and year separately, then pasted them together:
DTMO+"/"+DTDY+"/"+"20"+DTYR
into a character field. It exported as a date. May be ugly but it worked.