Converting a character field to a date field 4

[asm338s]

Is there any way where I can convert a character field to a date field programatically. I am trying to match some records based on if they are >= date1(yymmdd) and <= date2(yymmdd). This won't work for 1999 and earlier.(99 > 01)

Thanks

 

[jimstarr]

Look at the CTOD() function.

Jim

 

[asm338s]

I have a character string that is 10 characters long, so I used this:
ctod(substr(field1,1,6))
result: / /

 

[flintstone42]

function convdate(inval)
return ctod(substr(inval,3,2)+&quot;/&quot;+substr(inval,5,2)+&quot;/&quot;+substr(inval,1,2))

this should work,
fox has a set rollover that default to 50,
any year over 50 is 19??, example 51 = 1951, under 50
is 20??, 49 is 2049.

 

[Scott24x7]

ASM,
You mention your character field is 10 characters long. Your original message assumes date is in the YYMMDD format. What does you 10 byte field look like? If it is in the form:

2001/01/10, SUBSTR would yield: &quot;2001/0&quot;

Which will yield &quot; / / &quot;

What you need is a better string manipualtion. Assuming you want to pass an 8 character string to CTOD. There are numerous ways to try this, but note that the CTOD function expects the YEAR to be your last digits, unless you have set your global DATE functionality to something other than the MM/DD/YYYY format. For best accuracy, I would reccomend this (where lcMYDATE is the name of your date field):

lcDATE = SUBSTR(lcMYDATE,6,3)+RIGHT(lcMYDATE,2)+LEFT(lcMYDATE,4)

My example from above will yield: &quot;01/10/2001&quot;, which can then be safely passed to CTOD. CTOD will accept either &quot;01/10/2001&quot; or &quot;01/10/01&quot; as valid date formats.

Thanks,
-Scott

 

[ramani]

HI
SET CENTURY ON
SET DATE YMD
LOCATE FOR BETWEEN(CTOD(myCharacterDateField)),date1,date2)
** where date1 and date2 are date format dates
Hope this helps you ramani :-9
(Subramanian.G)
FoxAcc
ramani_g@yahoo.com

 

[Scott24x7]

ASM
A minor correction to my code which read:

lcDATE = SUBSTR(lcMYDATE,6,3)+RIGHT(lcMYDATE,2)+LEFT(lcMYDATE,4)

It should actually be:

lcDATE = SUBSTR(lcMYDATE,6,3)+RIGHT(lcMYDATE,2)+&quot;/&quot;+LEFT(lcMYDATE,4)

The other &quot;/&quot; character was not being placed by any of the string extractions. The first one is coverd in the SUBSTR, the other two were not. Also, I think I was a little vague in my explaination as to WHY you were getting &quot; / / &quot;, upon re-reading it. To be more specific, the SUBSTR line that you are issuing:

ctod(substr(field1,1,6))

Is returning a blank date, because your date value looks like one of two things:

1) &quot;2001/01/01&quot; which will return &quot;2000/0&quot;, and when placed through the CTOD function will return &quot; / / &quot;

or

2) &quot;01/01/01&quot; which when run through your code will return &quot;01/01/&quot; which will also when placed through the CTOD function will return &quot; / / &quot;.

So, as mentioned before, you need better manipulation of your date strings before putting them through the CTOD routine. That should solve your troubles...

Sorry for the vagueness of my first response.

Thanks,
-Scott