Crystal Reports : Bad Date format string error

Question

I am a newbie to crystal reports, I have a crystal report for which I have a data source from which I am using some fields on to the report.

Crsytal Report Name : InventoryReport

Data Source : I am giving Stored Procedure -- GetInventoryData

Fields : ItemID, ShippedDate, ItemName

I have to get all items which are shipped in between FromData and ToDate of ShippedDate, so I am using formula {GetInventoryData;1.ShippedDate} in {?FromDate} to {?ToDate}

dataType of Shipped Date is String and I have to convert to Date as it needs to be compared but I am having issues in that...

ShippedDate value will be : 2011-04-19 16:02:14.0000000

I have to convert at crystal reports side only..

Please help me how can I cast it to date

Answer 1

actually even better don't use that formula .... in selection formula using the date range explained above

date(split({GetInventoryData;1.ShippedDate}," ")[1]) in {?daterange}

Answer 2

If you are using string, you may do a simple less-than or greater than like:

...where ShippedDate >= '2011-04-19 00:00:00' and ShippedDate <= '2011-04-19 23:59:59'

this is like:

...where ShippedDate >= '<from-date> 00:00:00' and ShippedDate <= '<to-date> 23:59:59'

This would work and you'll not have to cast to a date.

You may also use as (If it works for you):

...where ShippedDate >= '<from-date>' and ShippedDate <= '<to-date>'

Answer 3

one way ... create a formula called cvtDate

date(
tonumber(split({GetInventoryData;1.ShippedDate},"-")[1])
,
tonumber(split({GetInventoryData;1.ShippedDate},"-")[2])
,
tonumber(split({GetInventoryData;1.ShippedDate},"-")[3])
)

then instead of creating two date parameters.. create only one called daterange that allows range values under values options Then selection formula would be

{@cvtDate} in {?daterange}