Formula to format date string field in "DD/MM/YYY" to match date range parameter in Crystal Reports

I have a date field held in a string in "DD/MM/YYY"  format and want to use it in record selection criteria to match a date range parameter. I've tried converting it to a formula using:

Right ({Transit Analysis PO Details.CompletedDate},4 ) + '/' + Left(Right ({Transit Analysis PO Details.CompletedDate},4),2) + '/' + Left ({Transit Analysis PO Details.CompletedDate},2 )

but this does not work. Hoshould I change the formula to get it to work or do I need to take a different approach?
JamesJMcDonnellAsked:
Who is Participating?
 
bdreed35Connect With a Mentor Commented:
Is it DD/MM/YYY with a three digit year or DD/MM/YYYY with a 4 digit year?

Your logic should be like this:

date(val(Right ({Transit Analysis PO Details.CompletedDate},4)), val(mid({Transit Analysis PO Details.CompletedDate},4,2)), val(left({Transit Analysis PO Details.CompletedDate},2)))

Keep in mind that this will not pass to the database in SQL and will only be performed on the client side, thus being less efficient.
You can also create a SQL Expression convert the field to a date and then your record selection would look like this:

{%SQL Exp Name} in {?Date Range}
0
 
JamesJMcDonnellAuthor Commented:
It's DD/MM/YYYY. I'm only using it in Crystal having formatted it from a SQL Server database datefield in a DateView. I had tried mid earlier and when it didn't work found the Left(Right use in an answer on this site. Was wondering if there might be a problem with leading zeroes being dropped for dates like 01/06/2006 and that someone might  have formula making use of Ltrim etc. Thanks, bdreed95, I will try your version using Date and Val when I get back to my work PC to-morrow morning.
0
 
JamesJMcDonnellAuthor Commented:
Thanks BDreed35 your code works. I have  a connected problem now. I want to display the date range on the report and using a normal formula for this,
e.g. minimum({?DateRange}) & " to " & maximum({?DateRange}) the date comes out in MM/DD/YYYY format. This is not so easy to reaarange as leading zeroes are dropped as in e.g. 6/1/2006 to 6/30/2006. How might this conversion be done?
0
 
bdreed35Commented:
Write it this way:

totext(minimum({?DateRange}),"MM/DD/YYYY") & " to " & totext(maximum({?DateRange}),"MM/DD/YYYY")
0
 
JamesJMcDonnellAuthor Commented:
It turned out that the format of the date range was affected by settings on the server I was running the report on. Applying your formula instead of the standard one then caused it to come out as 06/DD/YYYY to 06/DD/YYYY !?
Thanks for your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.