date formula

[cjany]

The datefields in our database are in yyyymmdd format. I have a formula field that converts to mm/dd/yyyy (date(val(left(totext({TXPYMT.PYMTDATE},0,""),4)),val(mid(totext({TXPYMT.PYMTDATE},0,""),5,2)),val(mid(totext({TXPYMT.PYMTDATE},0,""),7,2)))

I also have another formula field that adds the weekday to the above formula field (WeekdayName (DayOfWeek({@PaymentDate}))& ", "& {@PaymentDate})

I want to sort the field with the weekday by the date. I have my sort assending by the PYMTDATE field, but when I refresh the report, the date is sorted by the weekday instead. How can I sort by the date??

 

[lbass]

Either group on or use your original date conversion formula for a record sort. If you choose to insert a group, you can convert the display in group expert->options->customize the group name->use a formula:

weekdayname(dayofweek({@convert}))

If you use {@convert} as a record sort, you can then select {@convert} in the detail section->format field->date->customize date and set the display there by choosing "long" for day of week and setting the other date components to none.

-LB

 

[cjany]

Thanks, the 2nd option was much easier. I was trying to make it way to complicated.

Cheryl

 

[dgillz]

Here is a much easier way to convert your YYYYMMDD fields to a true date:

Numbertodate({YourField}) //assuming its a numeric field

Numbertodate(val({YourField})) //assuming its a string

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"A fine is a tax for doing wrong. A tax is a fine for doing well.
" - unknown