date number compare

[smeyer]

Crystal reads the date field from my pervasive DB as 20021127 but I need to select on current date is there a way to convert this to a date or convert currentDate to string or number for a comparison?

 

[synapsevampire]

Sure, you can do either.

Your best bet is to place something in the record selection akin to:

{MyTable.MyDateValue} = val(totext(year(currentdate),0000,"&quot+totext(month(currentdate),00,"&quot+totext(day(currentdate),00,"&quot)

This assumes that it's a value and not text, if it's a text field, eliminate the val()

-k

Happy T-Day

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[mwhalen]

this is similar to what I'm looking at - is there anyway to do this is the SQL?

 

[dgillz]

you can download the NumberToDate() function from the crystal decisions website, or I can email it to you. NumberToDate() converts an 8 digit integer in a YYYYMMDD format to a real date. Macola and ACCPAC are two of the main packages that use this type of date, although thankfully the new version of Macola is going to use real dates.

NumberToDate({YourNumericDateField}) in CurrentDate

Should return the records you are looking for.

Let me know if you have any questions. Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[mwhalen]

hey I did try to download the function from the Crystal website and I tried installing it but I found the readme file not that easy to figure out. anyways, it didn't work - if you could email it to me that would be great - mwhalen@hotmail.com

Thanks!

 

[synapsevampire]

I'm not sure what the pervasive SQL equivalent would be, try using substring() in a SQL Expression.

Dates formatted as strings are always problematic, if you can generate a View which converts it to a date, then you can just point to the View in the future.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com