Link to home
Start Free TrialLog in
Avatar of anordquist
anordquist

asked on

Convert string parameter to datetime

I've got a problem similar to the one in https://www.experts-exchange.com/questions/23105743/I-need-to-convert-a-string-20080123-to-display-as-a-date-01-23-2008-in-Crystal-Reports.html.

The report is called from a 3rd-party application that only accepts user input for parameters as strings.  I need to convert a 4-digit string (yyyy) to compare it against a DateTime field.  I've been attempting to add on January 1st to the date.

StringVar sRawDate := ToText({?p01SinceYear} + '0101');

When I try to do the date conversion

CDate(left(sRawDate,4), mid(sRawDate,5,2), right(sRawDate,2));

I get an error saying that highlights the middle and right parameters, saying there are too many arguments.
Avatar of wykabryan
wykabryan
Flag of United States of America image

This should work for you...


StringVar sRawDate := ToText({?p01SinceYear}  + '0101');

Date(cdbl(left(sRawDate,4)), cdbl(mid(sRawDate,5,2)), cdbl(right(sRawDate,2)))
ASKER CERTIFIED SOLUTION
Avatar of bdreed35
bdreed35
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Forgot a paren:

Date(val({?p01SinceYear}),1,1)
Avatar of anordquist
anordquist

ASKER

I've put

Date(val({?p01SinceYear},1,1)

in as a formula field called f02SupYear ( and it saves cleanly.  When I'm in the Select Expert and try to create a criteria comparing against a DateTime field, the f02SupYear doesn't show up in the list of values I can select.  

So, I did an edit of the entire selection formula,

{entity.entity_type} = "I" and
not ({member.mem_cat} in ["MEDIA", "MLN", "MLR", "MNCHAM", "UNISON"]) and
{member.mem_stat} = "A" and
{class.descrip} startswith "Supervision" and
{class_sched.loc_id} in ["EA-PLY", "EA-STP"] and
{class_sched.start_dt} >= DateTime (2006, 01, 01, 00, 00, 00) and
{class_part.part_stat} in ["C", "E"]

and replaced the
{class_sched.start_dt} >= DateTime (2006, 01, 01, 00, 00, 00)
with
{class_sched.start_dt} >= DateTime (Val({?p01SinceYear}), 01, 01, 00, 00, 00)

and it works.  Since bdreed35 came up with the basis for the solution, I'm assigning the points there.