>>>Hi,
>>>
>>>I wonder what I am doing wrong in this case. Here is the SQL Select (simplified):
>>>
>>>*-- The fields in SQL Server table:
>>>*-- PurchDate - Datetime
>>>*-- Years - Integer (from 1 to 100)
>>>dToday = date()
>>>cWhereExpr = "'" + DTOS( cToday ) "' >= (PurchDate + Years) "
>>>select * from MyTable where cWhereExpr
>>>
>>>
>>>Example: PurchDate is 2018-01-05 and Years is 10. But the record is included in the resulting query.
>>>Shouldn't the PurchDate + Years being much higher than the current date?
>>>
>>>UPDATE: I think my problem is that I have to convert Years into years since SQL Server sees it as Days. But how? Should I multiply the Years by 365? Or there is a better way to convert the int number into years?
>>
>>So, the suggestion given by Borislav works very well (below) for SQL Server database)
>>
>>dToday = date()
>>cWhereExpr = ['] + DTOS( cToday ) [' >=DATEADD(year, Years, PurchDate) ]
>>select * from MyTable where cWhereExpr
>>
>>
>>How would I change the above for VFP database? Does VFP have function similar to the SQL Server's DATEADD()?
>>
>>TIA
>
>How does that work in SQL Server??? That doesn't look like a correct syntax at all. Where do you really write and execute it and it is 'working'?
>Also, why would you ever want to compare a string with a datetime value? Compare a datetime with a datetime.
>Having said that (and assuming you are using SQL pass through for SQL server:
>
>
>cWhereExpr = "?m.dToday >=DATEADD(year, Years, PurchDate)"
>
>dToday = date()
>SQLExec(m.handle, "select * from MyTable where " + m.cWhereExpr, "crsResult")
>
>
>For VFP you could do the same with using Gomonth().
>
>
>dToday = date()
>select * from MyTable where gomonth(PurchDate, Years*12) < m.dToday
>
>
>If dToday would always be "today", then you don't need that variable at all:
>
>
>cWhereExpr = "cast(getdate() as date) >=DATEADD(year, Years, PurchDate)"
>SQLExec(m.handle, "select * from MyTable where " + m.cWhereExpr, "crsResult")
>
>
>For VFP:
>
>select * from MyTable where gomonth(PurchDate, Years*12) < date()
>
Note that in SQL Server the field type is DateTime and in VFP it is Date.
Here is the exact version of filter expression that I use in the SQL Select (note that the AND in the begging of the expression is because there are other filter expressions):
dPassDate = date()
IF plSqlServer
cLifeExpFilter = " AND (PURCHASED IS NOT NULL AND LIFE_EXP IS NOT NULL AND LIFE_EXP > 0 AND ('" +
DTOS(dPassDate) + "' >= DATEADD( year, LIFE_EXP, PURCHASED)) ) "
ELSE
cLifeExpFilter = " AND (!EMPTY(PURCHASED) AND LIFE_EXP > 0 AND '" + DTOS(dPassDate) + "' >=
DTOS(GOMONTH(PURCHASED,LIFE_EXP*12)))"
ENDIF
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham