Help with Date Conversion...

[fattyfatpants]

Ok so I don't know if any of you have used Elite before, but it is a pain in the you know what...anyway, this is what I'm trying to do using CR 9...
It's a Year To Date report that takes in 5 criteria for record selection:
1) Joint Billing Group Id cannot be NULL
2) Matter Number cannot start with 10000
3) Billing Period has to be between 6/03 and 4/04
4) Period has to be between 6/03 and 4/04
5) Period End Date has to be between 6/30/2003 and 4/30/2004

Upon further examination I notice that the Billing Period and Period are Strings and not Dates...how would I convert the strings to dates?...I've tried to use In CDate(0603) to CDate(0404) but it gives me an error "String Required Here"...

Billing Period, Period, and Period End Date all contain only one value (i.e. Billing Periods are 0403, 0503, 0603...0404, Periods are 0403, 0503, 0603...0404, Period End Dates are 4/30/2003, 5/30/2003, 6/30/2003...4/30/2004)

Here is my Selection Criteria Formula:
{matter.mjnum} <> "" and //Billing Group Id
not ({matter.mmatter} startswith "10000") and //Matter Number
{periodt.peendt} in DateTime (2003, 06, 30, 0, 0, 0) to DateTime (2004, 04, 30, 0, 0, 0) and //Period End Date
{periodt.pe} in "0603" to "0404" and //Period
{cost.cbiper} in "0603" to "0404" //Billing Period

since Period and Billing Period are of String data type this is obviously going to treat the range like a number so it is going to include 0500, 0501, 0600, 0601, etc. and I don't want it to SO...how can I convert Period and Billing Period to Date data type so I can include the correct date ranges????

 

[IanWaterman]

What is the form of your data string

DateSerial(1999, 6, 15) Converts datestrings in this format to date.
Date(YYYY, MM, DD) Converts datestrings in this format to date.
dtstodate(Date Time String) Converts Date Time Strings to date

If your string is not in these formats, you can create a formula to convert into one of the above. However, this will have to be processed on the client PC as Crystal will not parse to database server.

Alternatively, create a View of the data and convert date string to date in the database. For example in Oracle

TO_DATE('Date String', 'DD-MM-YYYY')

Ian Waterman
UK Crystal Reports Consultant

 

[synapsevampire]

As Ian alluded to, you can create a date field on the database, though this won't be optimized by indexes.

You can also do so within Crystal by using a SQL Expression, which would use the same syntax as Ian demonstrated and you don't need a View.

Storing monthyear as a string is suspicious and smacks of a bad design. Storing them as yearmonth makes more sense because your from <yearmo> to <yearmo> would work.

This is probably very inefficient, but as a quick fix:

{matter.mjnum} <> "" and //Billing Group Id
not ({matter.mmatter} startswith "10000") and //Matter Number
{periodt.peendt} in DateTime (2003, 06, 30, 0, 0, 0) to DateTime (2004, 04, 30, 0, 0, 0) and //Period End Date
mid({periodt.pe},3,2)+left({periodt.pe},2) in "0306" to "0404" and //Period
mid({cost.cbiper},3,2)+left({cost.cbiper},2)
in "0603" to "0404" //Billing Period

Another way from Crystal would be to create a SQL Expression on the database which uses:

substring({periodt.pe},3,2)+substring({periodt.pe},1,2)

to create meaningful data.

Creating a date isn't required, though it might help with parameter date checking.

Locate something not too sharp but plenty rusty and introduce it to your dba, repeatedly.

-k