• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 761
  • Last Modified:

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

0
brad_f
Asked:
brad_f
  • 3
  • 2
2 Solutions
 
James0628Commented:
You'll get that error if there are any records where that field does not contain a valid date.  Maybe it's blank or null.  Or maybe it's something else entirely.

 What do you want to do if it's not a date?  Include the record or exclude it?  To exclude the record, put the following in your record selection formula (eg. Report > "Selection Formula" > Record):

not IsNull ({table.field}) and IsDate ({table.field}) and
 DateValue ({table.field}) in #your starting date# to #your ending date#

 Obviously, replace #your starting date# and #your ending date# with your starting and ending dates or date range parameter(s) or whatever.

 Note that that bypasses the use of a separate formula to convert the field.  It says if the field is not null and the field contains a date, convert it to a date and compare that to your date range.  If your field could ever be null, you need to check for that first with IsNull.  If the field will never be null, you can leave that part out.

 I used DateValue instead of DateTimeValue.  If your field contains a date (with no time) and you're entering a date range (not datetime), there's no point in adding the time to the converted date.

 James
0
 
James0628Commented:
I semi-retract the last thing I said.  There may be a reason to use datetime instead of date.  If you're talking about MS SQL, it does not have a date data type.  Just datetime.  If you use only the date, the test may not be passed to the server because of that.  If you use datetime, maybe it will.  With that in mind, you might want to try something like:

not IsNull ({table.field}) and IsDate ({table.field}) and
 DateTimeValue ({table.field}) in DateTimeValue (#your starting date#) to
 DateTimeValue (#your ending date#)


 In this version I explicitly convert each date value to datetime.  This doesn't guarantee that the test will be passed to the server, but if it isn't passed the other way, it may be this way.  You can check by going to Database > "Show SQL Query" to see what's being passed to the server.

 James
0
 
brad_fAuthor Commented:
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

0
 
brad_fAuthor Commented:
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!
0
 
James0628Commented:
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
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now