Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Cecil Champenois
  Where is Cecil Champenois?
 Little Elm
 Texas - United States
 Cecil Champenois
 To: Cetin Basoz
  Where is Cetin Basoz?
 Izmir
 Turkey
 Cetin Basoz
 Tags
Subject: RE: Putting dates in a certain order
Thread ID: 345035 Message ID: 345039 # Views: 35 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Thursday, May 17, 2012 3:03:34 PM         
   


> > The below query does not quite put the data in the order I am looking for. I want the dates to end up in an order such as:
> >
> > Start End
> > 20120502 20120515
> > 20120418 20120501
> > 20120404 20120417
> > 20120321 20120403
> > 20120307 20120320
> > 20120222 20120306
> > 20120208 20120221
> > 20120125 20120207
> > 20120111 20120124
> > 20111228 20120110
> > 20111214 20111227
> >
> > However, the dates with years starting with 2011 are the first to be listed.
> >
> >
> > SELECT DTOS(CTOD(ben_start)) AS Ben_Start, DTOS(CTOD(ben_end)) AS Ben_End ;
> > 	FROM AAG_BILLING ;
> > 	WHERE Bill_Amt > 0 AND ben_start <> ben_end ;
> > 	GROUP BY ben_start, ben_end ;
> > 	ORDER BY ben_start DESC, ben_end ;
> > 	INTO CURSOR tmpPeriods
> > 
> > * Eliminated any records not having the current year's value ("2012") in the BEN_START field.
> > SELECT * FROM tmpPeriods WHERE LEFT(ben_start,4)=lcYear INTO CURSOR tmpPeriods2
> > 
> > 

> >
> > What I then did was to get the current year, and store that value into a variable, such as lcYear, and then only selected the records having the current year. This worked for me, but I wonder if there is a way to accomplish the same thing without having to have two separate statements?
> >
> >
> >
> > Cecil Champenois, Jr.
>
>
SELECT DTOS(CTOD(ben_start)) AS Ben_Start, DTOS(CTOD(ben_end)) AS Ben_End ;
>  	FROM AAG_BILLING ;
>  	WHERE Bill_Amt > 0 AND ben_start <> ben_end ;
>  	GROUP BY 1, 2 ;
>  	ORDER BY 1 DESC, 2 ;
>  	INTO CURSOR tmpPeriods

>
> I think that is a bug.
>
> PS: Wouldn't that be better if you used date for dates.
>
>
>

> Cetin Basoz
>
> .Net has got better.Think about moving - check my blog:
> My Blog
> Blog (mirror) - sounds to be down
>

> Support Wikipedia

When I used the following statement, which uses the field as a CHARACTER data type formatted as mm/dd/yyyy, I still do not get what I desire which is to have the latest year's data show up at the top.
SELECT ben_start, ben_end ;
 	FROM AAG_BILLING ;
 	WHERE Bill_Amt > 0 AND ben_start <> ben_end ;
 	GROUP BY 1, 2 ;
 	ORDER BY 1 DESC, 2 ;
 	INTO CURSOR tmpPeriods

What I get with the above statement is the following,
12/28/2011 01/10/2012
12/14/2011 12/27/2011
11/30/2011 12/13/2011
11/16/2011 11/29/2011
11/02/2011 11/15/2011
05/02/2012 05/15/2012
04/18/2012 05/01/2012
04/04/2012 04/17/2012
and so on,...
So, the rows with the current year do not come out on top as I desire.


Cecil Champenois, Jr.

ENTIRE THREAD

Putting dates in a certain order Posted by Cecil Champenois @ 5/17/2012 2:40:29 PM
RE: Putting dates in a certain order Posted by Cetin Basoz @ 5/17/2012 2:52:24 PM
RE: Putting dates in a certain order Posted by Cecil Champenois @ 5/17/2012 3:03:34 PM
RE: Putting dates in a certain order Posted by Anil Sharma @ 5/17/2012 3:24:43 PM
RE: Putting dates in a certain order Posted by Cecil Champenois @ 5/17/2012 3:51:22 PM
RE: Putting dates in a certain order Posted by Anil Sharma @ 5/17/2012 8:06:37 PM
RE: Putting dates in a certain order Posted by Cetin Basoz @ 5/17/2012 3:32:13 PM
RE: Putting dates in a certain order Posted by Anil Sharma @ 5/17/2012 3:02:49 PM
RE: Putting dates in a certain order Posted by Anders Altberg @ 5/17/2012 3:04:08 PM
RE: Putting dates in a certain order Posted by Cetin Basoz @ 5/17/2012 3:34:23 PM
RE: Putting dates in a certain order Posted by Tamar Granor @ 5/17/2012 10:23:55 PM
RE: Putting dates in a certain order Posted by Anders Altberg @ 5/18/2012 12:53:05 AM