how to convert text to date 3

[jameys26174]

Hi All..
I am trying to convert a tbl thats text (but is actually a date that looks something like 20040609 --- like to read 06/06/2004) to date field in crystal?

Oracle as dbase. Similar text (date) fields are in several tables (not for sure if thats important).

Thanks,
J

 

[lbass]

You could use a formula like:

date(val(left({table.datestring},4)),val(mid({table.datestring},5,2)),
val(mid({table.datestring},7,2)))

Then you can right click on this formula and format it with the date format you prefer.

-LB

 

[jameys26174]

thanks a bunch..

Would I need to change data type to text and then apply/substitute this formula "totext" into your formula mentioned above?

Thanks,
J

 

[rosemaryl]

Dear Jameys...,

The easiest way, in my opinion, is to use a SQL Expression.

Do the following in Crystal:

Insert/SQL Expression

(SELECT TO_DATE(20040609) FROM DUAL)

Replace the string above with your actual field reference. For example:

(SELECT TO_DATE(Incident."Open Date") FROM DUAL)

To do so, copy the code below and paste in the SQL Expession Editor. Place your cursor inside the parens, scroll through the fields list in the SQL Expression Editor Report fields pane, find your field and double click. Your field name will be inserted.

(SELECT TO_DATE() FROM DUAL)

Now, you didn't say what version of crystal or what driver so I am using Crystal 8.5 with the CR Oracle 8 v 3.6 driver and this works flawlessly to return:

06/06/04 00:00:00

On my report.

You can then use Crystals Format/Field to display only the date ... obviously since no time part for the string was passed, it becomes midnight.

Regards,

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[Turkbear]

Hi,
If you want a Date why make it text - you already have text.

 

[lbass]

Yes, I was assuming that you meant your date was actually a string, and that you wanted to convert it to a date datatype. Rosemary's suggestion to use a SQL expression may be the most efficient method to use.

-LB

 

[synapsevampire]

The format stored here is generally one used for numerics, not strings. Perhaps this is why you've asked about converting it to text? If it's already text, why would you do so?

To return the current date from an Oracle server use:

(select sysdate from dual)

I think what they want for a SQL Expression would be:

to_date(table.field,'YYYYMMDD')

There shouldn't be a Select as this is converting a field in a table. not obtaining a new date.

In either case LB's will work just as well for smaller rowsets, but I agree with Rosemary and generally use SQL Expressions.

-k

 

[rosemaryl]

Dear SV,

Absolutely correct, To_Date without the select from dual does work, however you don't need to qualify the format or at least I didn't.

I do the select from dual as a matter of habit because some of my sql expressions against Oracle require it.

I report against a database that runs on both SQL and Oracle and they put a dummy table in sql called Dual so that statements that work on the SQL side which often doesn't require a select from will work in Oracle which sometimes does, depending upon what I am doing.

I get into habits like that so I don't have to go ... oh duh I need from Dual here.

Thank you for clarifying that!*

Regards to you,

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[synapsevampire]

Hiya Ro, always good to see the big guns out

Most avoid Crystal's advanced features, and using the database to do the heavy lifting, you always have an interesting approach.

-k

 

[jameys26174]

ahhh...
Thank you all so much.

J