Converting Date Strings to DateTime format

[INPHOman]

I have a CRystal Report that uses a DateTime prompt in a formula to do record selection. My problem is I can only prompt for string values from CrystalWeb or ReCrystalize. What I need to do is, withing the formula, convert the string value to a DateTime value.

I will be prompting for two dates. start date and end date. I need to "tack on" a time value when I do the conversion to DateTime - the reports query for records entered between 6 AM on the start date to 6 AM on the end date.

So I need the following:

Start Promt = (2000,10,4) convert to 10/04/2000 06:00 AM

Can someone provide me the conversion formula???? [sig][/sig]

 

[kenhamady]

The syntax for a literal datetime value is:

DateTime (yyyy,mm,dd,hh,mm,ss)

You need 6 numeric arguments with this funciton. So if you can fill in the first three numerics from your prompt string, you can just add:

06,00,00) for 6am or
18,00,00) for 6pm


I am a bit confused becuase you say that the prompt is a string, but you also say your prompt value is (2000,10,4) which looks like part of the date function.

If you really have a string you will need to substring out the threee numbers and "VAL" them into numerics so that they will work in the function

[sig]<p>Ken Hamady-

http://www.kenhamady.com<br><a

href=

http://www.kenhamady.com/>Crystal

Reports Training by Ken Hamady</a><br>[/sig]

 

[phoward]

This is what i found and use often:

KBase Article: Date fields are brought in to Report Designer as 22 char string fields.

Date fields are brought in to Report Designer as 22 char string fields.

The information in the article refers to:
Seagate Crystal Reports 7

Applies to:
Reported version only
Connectivity_ODBC
Oracle
Incorrect Data

Synopsis

Report is based off a SQL Designer query.

Date fields are brought in to Report Designer as 22 char string fields.

The date range parameter field is the desired field for prompting.

Solution
Create a formula field to convert date/time string field to a date field.

i.e. @Convert
DateTimeToDate(DTSToDateTime({Query.DateTimeString}))

Then, create two parameter fields. (or one)
StartDate and EndDate. Both parameter fields must be of type Date

Then, go to Report | Edit Record Selection | Record

Type in the following:

@Convert > ?StartDate AND @Convert <= ?EndDate

Refresh the report and this will prompt for dates and dates from the calendar will be available for selection.

Hope it helps you out...

phoward@labsoftlis.com
[sig][/sig]