Converting text to date 2

[Bennie47250]

Using Crystal reports version 14.0.4.738 RTM
Reporting on a MS SQL table

Our CRM package stores dates in a text field as 2016-03-30.

They had provided a custom DLL to convert the field to a real date field to allow using the Crystal date functions such as LastFullWeek, MonthToDate, CurrentDate, CurrentDate -1. The DLL was updated each time the CRM provider provided a upgrade to Crystal. However the last DLL I have was written for Crystal 7.0.

I'm having some issues with Crystal 14 using the DLL and would like to change the formulas to use standard Crystal 14 functions.

What functions would I want to use to convert the text 2016-03-30 to a real date?
What function would I want to use to convert a real date to text that looks like 2016-03-30?

Thanks
Bennie

 

[AndyMc]

This formula should work:

numbervar yr := val(left({your text-date field},4));
numbervar mt := val(mid({your text-date field},6,2));
numbervar dy := val(right({your text-date field},2));
date(yr,mt,dy)


This will turn the text field into a date field.

 

[CrystalizeCanada]

Date("2016-03-30") turns the string into a real date so if your field is called MyDate then try
Date({table.MyDate}) and see if that works

Gordon BOCP
Crystalize

 

[Bennie47250]

Thanks all. I found that Crystal has a built in Type Conversion function CDate (number or string) built into it and it converted the field just fine.

 

[CrystalizeCanada]

Crystal reports has several functions that go by more than one name.
So CurrentDate and Today for example.
And CDate and Date are another example.

Gordon BOCP
Crystalize