convert text to date / datetime and use as selection criteria

Posted on 2009-04-20
Last Modified: 2012-05-06
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

Question by:brad_f
    LVL 34

    Accepted Solution

    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.

    LVL 34

    Assisted Solution

    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.


    Author Comment

    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 ]),

    Open in new window


    Author Closing Comment

    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!
    LVL 34

    Expert Comment

    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.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
    Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now