Print Page | Close Window

cdate function

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=1264
Printed Date: 03 May 2024 at 8:37am


Topic: cdate function
Posted By: foxy
Subject: cdate function
Date Posted: 03 Sep 2007 at 12:51am
Hi all,
 
I am trying to convert a string field to a date field.
 
The string field at the minute in the format: JUL-06, AUG-06, DEC-06, JUN-07, AUG-07, ... ...
 
This monthly period is the basi sof my grouping, but when I sort the groups it obviously sorts them in alphabetical order because it is a string field.
 
I need to sort them in date order. I dont really want so do it manually using specified order because there will be new periods added constantly.
 
Will this cdate function work to convert this string?
 
CDate ({TABLE.PERIOD})
 
Thanks


Replies:
Posted By: rnareshk
Date Posted: 03 Sep 2007 at 2:20am
have you check with the function Date(<string>).

-------------
Thank You,
Naresh.

Posted By: hilfy
Date Posted: 04 Sep 2007 at 4:10pm
Because your field is not a complete date, I doubt it will work.  You'll have to either reformat the string into a format like "JUL 1, 2006" or break it down into numbers and use the Date function, something like this: 
NumberVar Year;
NumberVar Month;


Year = 2000 + toNumber(right({table.Period}, 2));
Switch(
 left({table.Period}, 3) = 'JAN', Month = 1,
 left({table.Period}, 3) = 'FEB', Month = 2,
 left({table.Period}, 3) = 'MAR', Month = 3,
...);
CDate(Year, Month, 1)
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: foxy
Date Posted: 05 Sep 2007 at 3:05am
Thanks very much for that hilfy. I have put this into my formula now & will be testing it when i next pick up this project.
 
Thanks


-------------
FOXY


Print Page | Close Window