Crystal Reports Covert string field to date and pass date range

Technical Questions

Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions

Topic: Covert string field to date and pass date range

Author	Message
punty Newbie Joined: 08 May 2008 Online Status: Offline Posts: 5	Topic: Covert string field to date and pass date range Posted: 08 May 2008 at 9:18am
	Hi All, One of the column which is of varchar datatype stores date information in string format like JAN-00, AUG-06, DEC-06, JUN-07, AUG-07. My requirement is to convert this into date format and use it in selection formulas. I want to pass date range values against this column. Any help on this is highly appreciated. Thanks in adavnce.
	IP Logged


Lugh Senior Member Joined: 14 Nov 2007 Online Status: Offline Posts: 377	Posted: 08 May 2008 at 9:30am
	Your biggest challenge here is that you can't have dates without day values. The simplest solution is to consider each value to represent the first of each month. DateValue('01-' & {MyReport.MyDate}) I think that Crystal should recognize that (e.g., '01-Jan-06') as a valid date format.
	IP Logged

hilfy Admin Group Joined: 20 Nov 2006 Online Status: Offline Posts: 3702	Posted: 08 May 2008 at 2:08pm
	Then, if you want a range from the first day of the month to the last day of the month, you'd get your end date with something like this: DateAdd('m', 1, DateValue('01-' & {MyReport.MyDate})) - 1 The "DateAdd" will add a month to your date. By then subtracting 1 you get the last day of the month from your date. -Dell
	Proviti, Data & Analytics Practice www.protiviti.com/US-en/data-management-advanced-analytics
	IP Logged

punty Newbie Joined: 08 May 2008 Online Status: Offline Posts: 5	Posted: 09 May 2008 at 9:09am
	If I have to use the same in my selection formula eg: DateValue('01-'& {gl.balance.period_name}) in {?pmstartdate} to {?pmenddate}, I'm getting a "Bad date format string" error. Please advice.
	IP Logged

hilfy Admin Group Joined: 20 Nov 2006 Online Status: Offline Posts: 3702	Posted: 09 May 2008 at 10:41am
	Rather than editing the selection criteria, try creating a formula with the calculation and then use that. The drawback to this approach is that it will pull all of the data to the workstation and then do the filtering there instead of doing it on the database (if you're working with a Client/Server db that does processing on the server.) Another way might be to create a SQL expression and see if you can use that in the selection criteria. What's in the expression will be based on your database type. In Oracle it would be: To_Date('01-'\|\|{gl.balance.period_name}) If that doesn't work, you may have to see about using a command instead of tables. -Dell
	Proviti, Data & Analytics Practice www.protiviti.com/US-en/data-management-advanced-analytics
	IP Logged

punty Newbie Joined: 08 May 2008 Online Status: Offline Posts: 5	Posted: 14 May 2008 at 9:56am
	It worked. Many thanks for your help on this.
	IP Logged

Printable version

Forum Jump

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum