Date in Crystal returns 8 digit number

[barra47]

I am very new to Crystal reports
and I creted a rpt based on an MS SQL table and the date field "TDATE" 25/08/2011 appears as a number on the report
e.g. 20,110,825.00

I have created a formula that converts it into a date
I got this formula from this site and it returns the number as a date on the report

WhilePrintingRecords;
NumberVar input := {PMTIMED.TDATE};
// This line checks for a minimum value, any value will work.
If input < 19590101 then Date (1959, 01, 01) else
Date ( Val (ToText (input, 0 , "") [1 to 4]),
Val (ToText (input, 0 , "") [5 to 6]),
Val (ToText (input, 0 , "") [7 to 8]) )

But now i want to create a prompt for the TDATE field but the TDATE field is that number as above, How do I create a parameter or promt where the user inputs the date 25/08/2011 and it then relates it to the number
Hope that makes sense

Thanks in advance



Can I build the parameter based on the formula

 

[briangriffin]

The first thing to do is find the person who stored dates that way and beat him senseless.

Next, it would be easier to do all this on the database side - can you create views? If so, it would be something like:

select
convert(smalldatetime,cast(TDATE as varchar(12)),112) as actualdate,
*
from yourtable


Then use the view in place of the table when constructing the report. Your parameter could then reference the new date field and everything works more easily.

If you can't create a view on the database, you could do the same thing with a command object within the report. However, since you'll probably be reporting off this table again, and that date field will probably be referenced again, it makes more sense to create the view if possible.

 

[lbass]

If you remove the "whileprintingrecords" you can set your conversion formula = to a date parameter in your record selection formula:

{@date} = {?date}

This will not be as efficient as Brian's suggestion though, so if you are able, try his suggestion.

-LB