Date calculation

[PSFMIS]

I have not figured out how this could be done in VFP and I was wondering if someone could help.

I want to take a date in this format 02-17-2006 and subtract another date 02-10-2006 to the get number of days between these two dates. Is this possible? I have already found that I can't use the DateDiff command.

-Aaron

 

[bborissov]

LOCAL ldDate1, ldDate2
LOCAL lcDate1, lcDate2
lcDate1 = [02-17-2006]
lcDate2 = [02-10-2006]

ldDate1 = DATE(VAL(RIGHT(lcDate1,4)),VAL(SUBSTR(lcDate1,4,2)), VAL(LEFT(lcDate1,2)))
ldDate2 = DATE(VAL(RIGHT(lcDate2,4)),VAL(SUBSTR(lcDate2,4,2)), VAL(LEFT(lcDate2,2)))
? ldDate2 - ldDate1

Borislav Borissov

 

[Stella740pl]

Sorry, Borislav, but I don't think this is necessary. When you subtract two dates in VFP, you always get the number of days as a result. It's built-in behavior.

So this should work:

SET DATE TO USA
SET CENTURY ON 
[COLOR=green]* The 2 statements above will give you the format you want[/color]

LOCAL lcDate1, lcDate2
lcDate1 = {02-17-2006}
lcDate2 = {02-10-2006} 
[COLOR=green]* These brackets will give you the date type, not character[/color]

? ldDate2 - ldDate1
[COLOR=green]* Just subtract them, you will get the number of days[/color]

 

[PSFMIS]

I was hoping there would be a simple command like datediff that I could use in an SQL string... Is this the only way to do this?

-Aaron

 

[Stella740pl]

Is this the only way to do this?

No, not only. See my reply - is simple subtraction simple enough for you? ;-)

 

[bborissov]

Always forget that DATE TO USA is mm-dd-yy

When I check HELP I tought (I don't know what I tought) )))
Maybe I must stop for today )) I NEED BEER

Borislav Borissov

 

[Mike Lewis]

Aaron,

Stella is right. You don't need DateDiff() in VFP. You simply subtract one date from the other.

However, the code that both Stella and Borislav suggested will fail in a default environment in VFP 6.0 and above. You need to change the actual date constants, from:

{02-17-2006}

to:

{^2006-17-02}

This is the so-called unambiguous format for date constants in 6.0 and above. Alternatively, you could issue SET STRICTDATE TO 0 before you do the calculation. This will allow VFP to work with the older format, but this is dangerous because it is sensitive to the way the date is formatted.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Stella740pl]

Mike, you are right. The part about STRICTDATE I tend to forget. Since I handle a lot of converted older code, my SET STRICTDATE is set TO 0 permanently.

 

[Stella740pl]

Borislav,
Always forget that DATE TO USA is mm-dd-yy
Well, I forget it sometimes, too. My DATE is set TO AMERICAN :-D (mm/dd/yy).

 

[PSFMIS]

I'm trying to get the Date Difference between today's date and the date in a field called created which is in this format: YYYYMMDDHHMMSS.

Is This close??

{(^"& Year(Date) &"-"& Right("0" & Month(Date),2) &"/"& Right("0" & Day(Date),2)")} - {(^LEFT(qccreated,4))+'-'+SUBSTR(qccreated,5,2)+'-'+SUBSTR(qccreated,7,2)}

I want to put this in an SQL string when I get it working.

Thanks,
-Aaron

 

[Mike Lewis]

Aaron,

Your code is unnecessarily complicated. If you have a field called Created, the following code will give you the difference between Created and today's date:

DATE() - Created

You can put this in a SQL SELECT, for example:

SELECT Created, DATE() - Created FROM Mytable

or

SELECT * FROM MyTable WHERE DATE() - Created > 5

One possible gotcha: Make sure Created is really a date, not a datetime, otherwise the above code will give a type mismatch error. To fix it, use TTOD(Created) rather than Created.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[MarciaAkins]

I'm trying to get the Date Difference between today's date and the date in a field called created which is in this format: YYYYMMDDHHMMSS.

DATE() - TTOD( Created )

Marcia G. Akins

http://www.TightlineComputers.com

 

[suchat]

I'm trying to get the Date Difference between today's date and the date in a field called created which is in this format: YYYYMMDDHHMMSS.

Assuming that created is a character field. There are several way to convert a "YYYYMMDDHHMMSS" string into a date type variable. Evaluate() and Transform() with R format code is easy to read and the conversion is not affected by current date format setting.

Date() - Evaluate(transform(created, "@R {^9999-99-99}"))

is the date difference you want.

- - -

 

[PSFMIS]

suchat, I don't understand what this is doing... Could you explain it to me.

Evaluate(transform(created, "@R {^9999-99-99}"))

Thanks

 

[suchat]

If today is 24 feb 2006:

{^2006-02-24} is a literal expression for today, it is a date type expression same as what DATE() returns.

In order to get {^YYYY-MM-DD} from a string variable (created in your case) I choose to use Transform() and Evaluate().

With Transform(created, "@R {^9999-99-99}"); created was tranformed according to the template supplied after the @R format code. The HHMMSS part was dropped out by the process.
Then Evaluate() "change" [{^2006-02-24}] into a date variable.