Crystal Reports Grabbing the oldest and newest date

Report Design

Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design

Topic: Grabbing the oldest and newest date

Author	Message
crmetal1 Newbie Joined: 30 Oct 2012 Online Status: Offline Posts: 5	Topic: Grabbing the oldest and newest date Posted: 02 Nov 2012 at 6:46am
	I am trying to get the oldest and the newest date from a larger group of dates. Min and Max don't work for me because of the format crossing months and years so it sorts by the day first. Does anyone know how to do this? I don't know crystal that well so you may need to spell out the formula!! Edited by crmetal1 - 02 Nov 2012 at 6:48am
	IP Logged


lockwelle Moderator Joined: 21 Dec 2007 Online Status: Offline Posts: 4374	Posted: 02 Nov 2012 at 8:40am
	try this, create a formula like: totext({table.datefield}, "yyyyMMdd") then get your min and max on that date. if you need to, you can always convert the string back: you could even right a function, but this will work as well. local stringvar x := mindate; mid(x, 5,2) + "/" + right(x,2) + "/" + left(x,4) at least this should give you some ideas of how to proceed...I haven't use the Min & Max aggregates
	IP Logged

crmetal1 Newbie Joined: 30 Oct 2012 Online Status: Offline Posts: 5	Posted: 02 Nov 2012 at 10:12am
	I tried the first one but I get the error "To many arguments in this statement" The formula looks like this totext({TimeTicketDet.TicketDate}, "yyyyMMdd")
	IP Logged

comatt1 Senior Member Joined: 19 May 2011 Online Status: Offline Posts: 337	Posted: 02 Nov 2012 at 10:21am
	were you converting the date field within a command you could do this. CONVERT(VARCHAR(8), {TimeTicketDet.TicketDate}, 112) However, Locks answer should work. Should provide the field type and some sample data, lock can def direct you in the right direction lock > me Edited by comatt1 - 02 Nov 2012 at 10:32am
	IP Logged

lockwelle Moderator Joined: 21 Dec 2007 Online Status: Offline Posts: 4374	Posted: 02 Nov 2012 at 10:55am
	for the error, is the field a datetime field? If it isn't that error would make sense, but I am pretty sure that totext(field, format pattern) is a correct use for a datetime. Check with Help for the ToText function for more clarification. commatt1 solution works as well, though I use that in SQL, and haven't ever tried it in CR directly.
	IP Logged

crmetal1 Newbie Joined: 30 Oct 2012 Online Status: Offline Posts: 5	Posted: 02 Nov 2012 at 11:06am
	It says it is a string 01/02/12 is a sample for jan 2nd 2012
	IP Logged

comatt1 Senior Member Joined: 19 May 2011 Online Status: Offline Posts: 337	Posted: 06 Nov 2012 at 3:55am
	just convert to a date then. isdate({field}) then cdate({field)) Should be able to sort from there. using min/max
	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