date manipulation 1

[SMCmtg]

Hi: I have imported from excel cols and rows into mytable into fields/records.

Date comes over as 020321 importing it as characters ("type=numeric&quotin mydatefield, How can I convert it to the date format of 03/21/2002 into a different field?

Thanks for any help. . .(dates always give me a headache!)

 

[5151]

lnDate = <field>
lcDate = SUBSTR(lnDate,1,2) + &quot;/&quot; + SUBSTR(lnDate,3,2);
+ &quot;/&quot; + SUBSTR(lnDate,5,4)
ldDate = CTOD(lcDate) &&Valid Date Format

 

[rgbean]

Assuming all the dates are within a hundred years, I'd add a new column myddate (type date), and use the following:
REPLACE ALL myddate WITH ;
DATE(iif(mydatefield/10000 > 50, 1900+mydatefield/10000, ;
2000+mydatefield/10000), ;
MOD(mydatefield / 100, 100), ;
MOD(mydatefield, 100))

You may have to adjust the 50 to match your data.

Rick

 

[ramani]

HI SMCmtg,

Before exporting, you can obtain the numeric field of date as a date field.

For making the convertion follow the steps..

1. SET DATE YMD

2. Use followinf function to convert your numeric field into a date field..

myDtField = &quot;20&quot;+LEFT(ALLT(STR(NumDate),2) + &quot;/&quot; ;
SUBSTR(ALLT(STR(NumDate),3,2) + &quot;/&quot; ;
RIGHT(ALLT(STR(NumDate),2)

OR && assuming date not exceeding year 2010... and has date also in 20th century..

myDtField = IIF(LEFT(ALLT(STR(NumDate),1)=&quot;0&quot;,&quot;20&quot;,&quot;19&quot+;
SUBSTR(ALLT(STR(NumDate),1,2) + &quot;/&quot; ;
SUBSTR(ALLT(STR(NumDate),3,2) + &quot;/&quot; ;
SUBSTR(ALLT(STR(NumDate),5,2)

This function can be used strightaway in your SQL extract so that the date field is right in place for your conversion.

An alternative way is to convert your date field to numeric number of days from 1st jan 1999 ... export to excel as numeric column... set the numeric field column as Date type under format menu choice or programaticaly from VFP. Excel stores the date cell as the number of days from 1stJan1999. BUt surely this is not convenient way for your needs. The information is just for knowledge.

5151.. missed on the substr with 5,4 instead of 5,2

I think Rick was in a hurry and left you to assume on the data type and convertions within the DATE() function.

Hope this helps you ramani :-9
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[rgbean]

I wrote my response just before I left last night, and shortly realized a small promblem with my code - the rounding involved with the division. So here is a modified version that really should work, all in one statement.

REPLACE ALL myddate WITH ;
DATE(iif(int(mydatefield/10000) > 50, ;
1900+int(mydatefield/10000), ;
2000+int(mydatefield/10000)), ;
MOD(int(mydatefield / 100), 100), ;
MOD(mydatefield, 100))

Note to Ramani: I'm not quite sure of your concern. As I suggested, the myddate field is a separate DATE field added to the table. I assumed that he would then delete his NUMERIC mydatefield field and rename (if necessary) the 'real' DATE field. This technique works with the data he has, rather than requiring another import.

Rick

 

[SMCmtg]

Rick & Ramani:
Was out of pocket for a while and just got back to your feedback(s). Took a little bit, but I got it working. . .THX

One question to Rick? What is the &quot;50&quot; for/do in the following line of code?

DATE(iif(int(mydatefield/10000) > 50, ;

Could you write it out in long hand and not code? THX

Bill

 

[rgbean]

Bill,
Because you only had the &quot;year&quot; YY, and the date() function requires a full CCYY, this iff() was meant to set the &quot;proper&quot; century. So for values 00 -> 49, it assumes the value is 2000 -> 2049, and 50 -> 99 are 1950 -> 1999. By adjusting the 50, you can handle other date range assumptions. e.g. If the field was birth dates, then right now instead of 50, 02 would make more sense. If the field represents contract expiration dates for a company founded in 1980, then 79 would be a good split point.

Rick