An equivalent of the following in Crystal

[karnd]

Hi,

The date field in the Database is '03/31/2005' but i have to show this way in Crystal Reports
like March,2005
Could you please give me the solution.

Thanks a lot,
Regards,

 

[synapsevampire]

Right click the date on the report canvas and select Format Field->Date->Customize and select the format you'd like in the Format section.

-k

 

[karnd]

Hi ,

Thanks for the quick reply but this i have to physically do it by using conversion function like to_char(Date,'Month') but crystal dont allow me to do it with that function so i am looking for an equivalent.

This expression i will be using it for creating a prompt.

I hope you got my point.

Thanks a lot,

 

[cmv131]

monthname(month(yourfield))

year(yourfield)

 

[karnd]

Hi,

the database field's datatype is Varchar so it is giving an error with the above syntax.

Note:Basically this i am doing it in Business View Manager for creating Month,Year field under SQL Expression.

But the above did not help me out.any ideas please...

Thanks a lot,

 

[cmv131]

If it is in that format consitantly

monthname(ToNumber(left(yourfield,2))) 
+ ',' 
+ right(yourfield,4)

 

[synapsevampire]

Convert it to a date first in a formula:

cdate({table.field})

Then you can treat it as a date.

Stating that it was a date field in the database wasted everyone's time, state that it's a character field with a date.

-k

 

[karnd]

Hi All,

I understood from yourside but the Business View Manager should not be recognising cdate({table.field}) and also ToNumber & MonthName function so i am getting the follwoing error message:
SQL Parsing Error:
42000:[Microsoft][ODBC SQL Server Driver][SQL Server]'monthname' is not a recognised function name.

Please let me know where i am doing wrong

Thanks for your kind cooperation.

Regards,

 

[synapsevampire]

you state that you need to use a database function, which is untrue, and you seem to be answering part of the answers supplied.

Try my solution, it should fix it immediately.

As for using a database side solution, consider that NOT posting your database type NOR the version of Crystal requires guesswork by others.

If you want to use a SQL Expression, then use whatever the syntax is for your database to convert it to a date first, simple stuff really.

Whatever the database is, DON'T use a to_char (I assume that you're using oracle), convert the char based date field to a date and just use crystal to format it. if you return a date as a text element, you will lose lookup, grouping and sortation capabilities on that field.

If you insist on doing so, then post in your database type forum here for the syntax for returning what you need as this is a database function question, not Crystal.

-k