Search code examples
crystal-reports

create a formula to convert 1 varchar field w/date and 1 varchar w/time into 1 datetime field

I have an existing report that has 2 varchar fields. 1 holds a date, 2 holds a time. I need to create a formula concatenating both and then use that formula to sort the report by. Example: 

SCHEDULED_DATE             SCHEDULED_TIME
2018-04-26 00:00:00.000    1:00 PM
2018-04-26 00:00:00.000    NULL

I want to add the 2 fields together and then sort. In SQL I can do the following but in crystal reports, it fails to convert properly and throws an error.

ORDER BY Convert(Datetime, SCHEDULED_DATE + Convert(time,ISNULL(SCHEDULED_TIME, '00:00:00.000')))

Here is my attempt at the Crystal Report formula:

If IsNull ({Inspections.SCHEDULED_TIME})
Then CDateTime(CDATE({Inspections.SCHEDULED_DATE}) + Time('00:00:00.000'))
Else CDateTime(CDate({Inspections.SCHEDULED_DATE})+ CTime({Inspections.SCHEDULED_TIME}))

Any help would be greatly appreciated. Thanks

Solution

  • First of all, date and time values should never be stored as text.
    Better use the appropriate data-types of your DBMS.

    Then see multiple issues in your formula:

    • CDate and CDateTime don't support milliseconds
    • Date and Time values can't be concatenated with a +
    • The SCHEDULED_DATE-column already contains a time-part and you (try to) add one more
    • When concatenating the date and time part, separate them with a blank space

    So, you have to remove the time part and concatenate the columns without converting them to Date or Time:

    If IsNull ({Inspections.SCHEDULED_TIME})
Then CDateTime(CDATE({Inspections.SCHEDULED_DATE}))
Else CDateTime(Left({Inspections.SCHEDULED_DATE},10)+" "+{Inspections.SCHEDULED_TIME})

    Note: I removed the time 00:00:00.000 in the "True"-part completely as it's not required when you set all to zero anyway.