Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Anders Altberg
  Where is Anders Altberg?
 Uppsala
 Sweden
 Anders Altberg
 To: Cecil Champenois
  Where is Cecil Champenois?
 Little Elm
 Texas - United States
 Cecil Champenois
 Tags
Subject: RE: Putting dates in a certain order
Thread ID: 345035 Message ID: 345088 # Views: 29 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Friday, May 18, 2012 12:53:05 AM         
   


> 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.

Given that your dates are Char type and stored in DMY format, and you want them returned as char in YMD format but without datepart marks, and sorted chronologically instead of alphanumerically:

CREATE CURSOR Xx (ben_start C(10), ben_end C(10))
INSERT INTO Xx VALUES ("05/02/2012", "05/15/2012")
INSERT INTO Xx VALUES ("04/18/2012", "05/01/2012")
INSERT INTO Xx VALUES ("04/04/2012", "04/17/2012")
INSERT INTO Xx VALUES ("03/21/2012", "04/03/2012")
INSERT INTO Xx VALUES ("03/07/2012", "03/20/2012")
INSERT INTO Xx VALUES ("02/22/2012", "03/06/2012")
INSERT INTO Xx VALUES ("02/08/2012", "02/21/2012")
INSERT INTO Xx VALUES ("01/25/2012", "02/07/2012")
INSERT INTO Xx VALUES ("01/11/2012", "01/24/2012")
INSERT INTO Xx VALUES ("12/28/2011", "01/10/2012")
INSERT INTO Xx VALUES ("12/14/2011", "12/27/2011")

SET DATE MDY
SELECT DTOC(CTOD(ben_start),3),DTOC(CTOD(ben_end),3) FROM Xx ORDER BY 1 DESC ,2 DESC
* or
SELECT DTOS(CTOD(ben_start)),DTOS(CTOD(ben_end)) FROM Xx ORDER BY 1 DESC ,2 DESC



Result:
Exp_1 Exp_2
20120502 20120515
20120418 20120501
20120404 20120417
20120321 20120403
20120307 20120320
20120222 20120306
20120208 20120221
20120125 20120207
20120111 20120124
20111228 20120110
20111214 20111227

-Anders

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