Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

custom style date format

Status
Not open for further replies.

PapaSmurf

IS-IT--Management
May 16, 2001
48
GB
Is there any way to edit the format of a datetime field to produce an output similar to my formula below:

totext(datepart("ww",{SHIPLINES.SHPMT_DATE})) + "/" + totext(datepart("yyyy", {SHIPLINES.SHPMT_DATE}))

I'm unable to use this field in selection criteria or as properly sorted date field in charts because it is a string.

I feel there must be a way to achieve this by applying a custom stlye to the datetime field...... is there?

Ben
 
If you are looking for a number from this formula, just convert yourexisting formula to a number by putting "ToNumber(" on the left side of it and ")" on the right side of it.

Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com
 
Not quite. Basically instead your usual mm/dd/yyyy I want a format such as ww/yyyy where ww is the week number in the year (up to 52). Reason being I want to compare delivery dates in that format as dates, because as strings it wont work!

Thanks
 
Try this formula:

totext(datepart("ww",{YourDate}),0)&"/"&totext(year({YourDate}),0,"") Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com
 
Kiss principle:

Create a formula which contains the format YYYYWW, and use that for sorting/grouping.

if len(totext(datepart("ww",currentdate),00)) = 1 then
val((totext(year(currentdate),0,"") +"0"+totext(datepart("ww",currentdate),0)) )
else
val(totext(year(currentdate),0,"")+totext(datepart("ww",currentdate),0) )

Create another formula which is the text version for displaying.

if len(totext(datepart("ww",currentdate),00)) = 1 then
"0"+totext(datepart("ww",currentdate),00)+"/"&totext(year(currentdate),0,"")
else
totext(datepart("ww",currentdate),00)+"/"&totext(year(currentdate),0,"")

Post an example of how you're using this field in a
selection formula if needed.

-k kai@informeddatadecisions.com
 
Thanks all, though seems like it's more difficult than I thought.

I need to keep the dates in date format, as 02/2002 >11/2002 as a string, which we know isn't true. Basically our deliveries are specified by week number, so if shipment week<=required week then the shipment is on time.

Any other thoughts?
 
Please ignore my last post, it doesn't make any sense!
 
OK, apologies if my descriptive powers are below par so far!

I've converting my date time field as below, and am using it to group by

stringvar shipweek;

shipweek:=totext(datepart(&quot;ww&quot;,{SHIPLINES.SHPMT_DATE})) + &quot;/&quot; + totext(datepart(&quot;yyyy&quot;, {SHIPLINES.SHPMT_DATE}));
if len(shipweek)=6 then shipweek:=&quot;0&quot; + shipweek;
shipweek;

I have a parameter field of type string allowing the user to enter range of values. I link this to shipweek in my selection criteria.

Problem is when the users enters 01/2002 and 08/2002 as the range, the groups that come out are as folows:

01/2002, 01/2003, 02/2001, 02/2002, 02/2003.. etc

So not only is the sort incorrect, but I'm getting dates I didn't mean to specify!
 
This is becuase as a string value, this is sorting alphanumerically. This is expected behavior. Have a look at specified order grouping.

Also, please post your record selection formula and we can figure out what is wrong there as well. Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com
 
I'm off on holiday for a week tomorrow so may be my last post.

My record selection criteria is simply;

{@Shipweek} = {?DateRange}

I've managed a work-around in which the user enters discrete values in the form ww/yyyy but this falls over when traversing a year, eg 52/2001....01/2002 get's reversed when sorted.
 

I do not understand what you just said about &quot;gets reversed&quot;. Please explain.

In regards to your record selection, if you have a range of &quot;01/2002&quot; to &quot;09/2002&quot;, every string begining with &quot;02&quot; thru &quot;08&quot;, REGARDLESS of the &quot;year&quot; portion of the string, will be included in your report. This is expected behavior.

You need to base your record selection on a real date and not the string you are displaying in your report. Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com
 
Use an inverse of the shipweek variable for sorting and grouping, while retaining the shipweek variable for display.

i.e. display

shipweek:=totext(datepart(&quot;ww&quot;,{SHIPLINES.SHPMT_DATE})) + &quot;/&quot; + totext(datepart(&quot;yyyy&quot;, {SHIPLINES.SHPMT_DATE}));
if len(shipweek)=6 then shipweek:=&quot;0&quot; + shipweek;
shipweek;

but sort on

If DatePart(&quot;ww&quot;,{SHIPLINES.SHPMT_DATE}) < 10
Then
totext(datepart(&quot;yyyy&quot;, {SHIPLINES.SHPMT_DATE})) + &quot;0&quot; + totext(datepart(&quot;ww&quot;,{SHIPLINES.SHPMT_DATE}))
Else
totext(datepart(&quot;yyyy&quot;, {SHIPLINES.SHPMT_DATE})) + totext(datepart(&quot;ww&quot;,{SHIPLINES.SHPMT_DATE}));

Naith
 
dgillz - thanks, &quot;gets reversed&quot; - as a string the sort order will be 01/2002 then 52/2001- not correct. I could do the selection on the real date, but if the user enters a date that isn't at the start of the week, then they may miss data from that week. It would be more user friendy to allow them to enter WW/YYYY in the criteria range - so has to be a string.

Naith- thanks a lot - though I'm not quite sure where exactly to put the sort formula. Shoud I create a formula for each line (x3) and add those formulae to the record sort order?

Many thanks!
 
I'm a total novice, but just by looking at your results, perhaps you'll get the sorting you want by setting the string value to YYYY/WW instead of WW/YYYY. I'm wondering if CR is looking at the entire string value, and sorting alphanumerically so 01/2003 is &quot;less&quot; than 12/2002 when you strip out the /.

If you reverse the order, 2003/01 would be a &quot;higher&quot; value than 2002/12, and then the sort would be correct.
 
Basically, you swap whatever WW/YYYY field you're grouping on with the inverted formula, so you're now grouping on YYYYWW.

While you would still display WW/YYYY, because you're now grouping on YYYYWW, instances like 01/2002 followed by 52/2001 are corrected; being as the grouping formula has them down as 200152 and 200201.

Naith
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top