asked on

convert text to date / datetime and use as selection criteria

I need to filter a crystal report by a date range where the dates in question are stored in an SQL table, in a field of type 'text'. (Crystal actually sees it as type "memo") I found out in a hurry that I couldn't actually use that field directly in the select criteria. So I thought I would write a simple formula (see code section) to convert the string date into a datetime format so that i could use the formula in the select criteria. I can see that the conversion seems to work ok by adding the output of the formula to the body of the report and things seem ok. It takes text like 03/04/2008 and changes it to 3/4/2008 12:00:00AM

I can use the select expert to add the formula as a field in the select criteria and it seems to work well as I say field is between and can pick two datetimes with no problems, then it asks if i want to refresh the data, and when it does, it generages the error: "Bad date-time format string."

Any help would be greatly appreciated.

// where the field below is the one with the date stored in text format
DateTimeValue ({table.field})

Open in new window

ASKER CERTIFIED SOLUTION

James0628

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

James0628

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

brad_f

ASKER

Thanks very much James, you're suggestions were insightful, and I'll keep them for future reference. I did some more searching on some other websites last night and found a formula that I wanted to try out and it worked quite well. See the code section for what I used. It appears that when I was 'converting' the string to the date using the similar formulas to what you had posted, it wasn't actually converting the numbers. I had to actually use the val() function to get the value of the numbers stored in the string... makes sense... i just wish i had found it sooner.

StringVar MyDate :=  {table.field};
DateTime (
  Val (DT [  1 to  4 ]),
  Val (DT [  5 to  6 ]),
  Val (DT [  7 to  8 ]),
  00,
  00,
  00)

Open in new window

brad_f

ASKER

while your suggestions / responses were very helpful and much appreciated, I ended up finding my own solution. points awarded for your time and effort. Thank-you!

James0628

Ah. Based on what you posted, it looks like the problem was that the string is in the form YYYYMMDD (as opposed to your original message, where you said "text like 03/04/2008"). YYYYMMDD is not a form that DateTimeValue will understand. YYYY/MM/DD would work, but without the "/"s, it has no idea what it's looking at. So, you get around that by manually pulling YYYY, MM and DD out of the field and using them separately.

Glad you found a solution.

James