Skip to Content
avatar image
Former Member

Date issue with formatting to standard of report

I have one column that is string but it is actually a date in this format YYYYMMDD and time as well. A typical date would be:

2017-07-01 05:53:07.100 -04:00

All my other dates on the row are mmddyy.

I tried this formula it does compile but is failing in runtime 'Bad date format string'

cDate(ToText(cDate({H1bac_2017_.RESULT_COMPLETIONDATE}),"MMddYYYY"))

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Jan 12 at 07:42 AM

    Hi Paul,

    See if this works:

    if {H1bac_2017_.RESULT_COMPLETIONDATE} <> '' then 
    (  
       if IsDate(Left({H1bac_2017_.RESULT_COMPLETIONDATE},10)) 
         Then Date(Left({H1bac_2017_.RESULT_COMPLETIONDATE},10))
    );

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 12 at 12:49 AM

    Hi Paul,

    Is your database field a String or DateTime? I'm trying to duplicate this but it depends on how that field is defined.

    The reason for your error is because you are trying to convert a string like "20180101" to a date. Crystal can't understand that date format, hence the error.

    The best you can do is do the initial CDate then format the formula by hand in the Format Editor.

    Add comment
    10|10000 characters needed characters exceeded