Date in Varchar format to March 1, 1999 format

[crash58]

Hello - I have spent the afternoon reading posts about similar problems and none of the solutions have worked so far. I am using CR8.5 and an SQL database; the data is in 10 character varchar YYYY-MM-DD.

I want a formula or a series of formulas to make this display as March 1, 1999.

thanks in advance. Could the stored hyphens be the cause of the big bruise on my forehead?

 

[lbass]

Maybe I'm missing something, but I think you should be able to just use date or cdate:

date(YYYY-MM-DD)

Then right click on the field->format field and choose:
"March 01, 1999" and if you don't want the leading zero for the day, choose "Customize" and format day to 1 place.

-LB

 

[Madawc]

You can also create a formula field for it,
totext ({your.date}, "MMMM d, YYYY&quot

Madawc Williams
East Anglia, Great Britain

 

[crash58]

When I do date OR cdate({mytable.field}) the formula editor accepts it, I place the formula in the report and when I run the report I get "Bad date format string" An isdate ({mytable.field}) returns true. However if I place the field in the report and right click on the field in the design mode, I do not get the format editor that applies to date format. CR still recognizes the field as a text field. I tried using the totext({field}, "MMMM D, YYYY&quot as suggested and got the error "too many arguments". I am obviously missing something - keep those ideas coming! Thanks...

 

[lbass]

This should work then:

totext(date({table.field}),"MMMM d, yyyy&quot

Please note that the case of the letters for month, day, and year do matter.

-LB

 

[crash58]

totext(date({Exhibitions.BeginISODate}),"MMMM d, yyyy&quot

returns "Bad Date Format String" when run in report. Hear that crash? It's my head... thank you for your help - if you have any other ideas, I'm wide open.

 

[lbass]

I think that isdate will just tell you that a field can be interpreted as a date, not that it is already a date datatype, so it makes sense that no date formats show up on right clicking when you place the field directly into the report, since it is a string, not a date.

If you place the field directly onto the report, what kinds of results are returned? Please provide sample results. Also, when you run the mouse cursor over the field, what datatype shows up in the tooltip text?

Most of the previous suggestions work when I try them, so I think there is some anomaly that we have to identify.

-LB