formatting a date 1

[maggielady]

If I have a date in this format: 29-Jan-95, how do I get that into a string format of yyyymmdd?

 

[JRB-Bldr]

If your date is in a DATE variable or field then you can use the following:

mdDate = {^1995-01-29}
mcDate = DTOS(mdDate)

Good Luck,
JRB-Bldr

 

[maggielady]

Thanks JRBBLDR, but it's in a character format.

 

[Stella740pl]

Well, I thought it's character, since 29-Jan-95 is not a recognized VFP date format (at least, through VFP6).

Here is one of the ways, but I can see a few more.

CREATE TABLE D:\TEMP\Date_Conv (Interdate C(9))
INSERT INTO Date_Conv (Interdate) VALUES ('25-Jan-95')
INSERT INTO Date_Conv (Interdate) VALUES ('02-Mar-04')
INSERT INTO Date_Conv (Interdate) VALUES ('12-Sep-00')

yr_Roll=50

DIMENSION aMon(12)

aMon( 1)="JAN"
aMon( 2)="FEB"
aMon( 3)="MAR"
aMon( 4)="APR"
aMon( 5)="MAY"
aMon( 6)="JUN"
aMon( 7)="JUL"
aMon( 8)="AUG"
aMon( 9)="SEP"
aMon(10)="OCT"
aMon(11)="NOV"
aMon(12)="DEC"

SELECT   IIF(VAL(RIGHT(Interdate,2))>=yr_Roll, ;
            '19'+RIGHT(Interdate,2), ;
            '20'+ RIGHT(Interdate,2)) + ;
         RIGHT('0'+ALLTRIM(STR(ASCAN(aMon, ;
            UPPER(SUBSTR(Interdate,4,3))))), 2) + ;
         LEFT(Interdate,2) AS new_date ;
   FROM Date_Conv ;
   INTO CURSOR conv_date

 

[maggielady]

This is the code I have so far and it works to a point:

x=alltrim(birthday1)
x1=alltrim(left(x,at("-",alltrim(x))-1))&&&day
x=alltrim(right(x,len(x)-at("-",alltrim(x))))
x2=alltrim(left(x,at("-",alltrim(x))-1))&&&month
do case
case x2="Jan"
x2="01"
case x2="Feb"
x2="02"
case x2="Mar"
x2="03"
case x2="Apr"
x2="04"
case x2="May"
x2="05"
case x2="Jun"
x2="06"
case x2="Jul"
x2="07"
case x2="Aug"
x2="08"
case x2="Sep"
x2="09"
case x2="Oct"
x2="10"
case x2="Nov"
x2="11"
case x2="Dec"
x2="12"
endcase
x=alltrim(right(x,len(x)-at("-",alltrim(x))))&&&year
This gives me 950129, now I need that to be in a string of yyyymmdd. I can't seem to get it to give me 1995 as the year and what if they were born in 2000? I want to thank everyone in advance for all their help. You people on this forum ROCK!!

 

[Stella740pl]

This gives me 950129, now I need that to be in a string of yyyymmdd. I can't seem to get it to give me 1995 as the year and what if they were born in 2000?

Take a look at the code I posted for you. It will give you all 4 digits in the year, you just have to set up your rollover point, e.g. if it's 49 or under, including 00, make it 2049 (or 2000, accordingly), if 50 or above, make it 1950, etc.

Thanks for the star!

 

[maggielady]

Thanks Stella740pl, works like a charm!! You rock!!

 

[Stella740pl]

Here is one more. I didn't notice any time difference on a small table, but am not so sure about a big one. You can test it on your real data.

CREATE TABLE D:\TEMP\Date_Conv (Interdate C(9))
INSERT INTO Date_Conv (Interdate) VALUES ('25-Jan-95')
INSERT INTO Date_Conv (Interdate) VALUES ('02-Mar-04')
INSERT INTO Date_Conv (Interdate) VALUES ('12-Sep-00')

yr_Roll=50

DIMENSION aMon(12)
FOR i=1 TO 12
	aMon(i)=UPPER(LEFT(CMON(DATE(2000,i,1)),3))
NEXT

SELECT  DTOS(DATE(IIF(VAL(RIGHT(Interdate,2))>=yr_Roll, ;
						1900+VAL(RIGHT(Interdate,2)), ;
						2000+VAL(RIGHT(Interdate,2))), ;
			ASCAN(aMon, UPPER(SUBSTR(Interdate,4,3))), ;
			VAL(LEFT(Interdate,2))))  AS new_date ;
	FROM Date_Conv ;
	INTO CURSOR conv_date