Change date format in Command of crystal reports

[swetham]

I have created a SQL command as,

select * from employee_Details where join_dt between {?start} and {?end}

In crystal reports, the date format is taken as mm/dd/yyyy. But the data is not getting retrieved if date is in that format. i am using MYSQL datbase. It is retrieving the data only when the format is yyyy-MM-dd. CAn anyone please help me on how to change the date format of parameter field.

 

[kray4660]

Since you are using a command, you would need to use the MySQL function(s) to convert the date. I know that the fumctions changed with newer versions of MySQL.

I hope this helps.

 

[lbass]

The format of join_dt shouldn't matter, as long as it is a date datetype and the parameters are, too. You do, however, have to enter the dates at the prompt using the format Crystal requests (in this case, yyyy-MM-dd).

-LB

 

[swetham]

I have used the dat_format function in MYSQL query then also it is not retrieving the data. How to enter the date in a particular format in crystal report when it prompts? We have the chance to select the date form datetimepicker only.

 

[lbass]

You can't change the date format in the parameter prompt. Can you confirm the datatype of the field? Place your field in the detail section of a Crystal Report and then right click on the field->browse.

-LB

 

[swetham]

The datatype of the parameter is date. In add command i have written the query as ,

select * from employee_Details where join_dt between {?start} and {?end}


In "Add Command" only i have created 2 date parameters start & End.

 

[lbass]

Okay, then I'm unclear on the issue. At the parameter prompt, you must enter the date in the format required. It doesn't matter how your date is formatted in the database, if it truly is a date datatype. So enter the selections using yyyy-MM-dd and you should get results.

-LB