subtracting 1 year in datetime format? 2

[zencalc]

Hello all;
In my select statement, I need to select all records such that
(currentdatetime - 1 year < {my.field} > currentdatetime)
How do I format the currentdatetime - 1 year part??
Thanks!
zen

 

[vidru]

DateAdd("yyyy", -1, CurrentDateTime)

I'd have tried to put together the rest of that formula as well, but the < {my.field} > currentdatetime just makes no sense to me.

-dave

 

[MColeman]

dateadd("y",-1,(yourdatefield))

Tek-Tips has some great info on dates in the FAQ section.

 

[synapsevampire]

This would be Crystal version dependent, and the database being used.

The above solutions are correct, however you should verify that the appropriate SQL is being passed to the database by selecting Databse->Show SQL Query.

-k

 

[tektipdjango]

synapsevampire,

Following your post, I want to say that there are two goals to achieve for the quality of the query.

1 - try to make the work on the server to avoid network load, and your suggestion to check the SQL is correct

2 - if there is an index on the field grap the opportunity to use it. For this the SQL - passed code is not the sole condition.

In this case the
yourdatefield > DateAdd("y", -1, CurrentDateTime)
formula is far better than the
DateAdd("yyyy", +1, yourdatefield) > CurrentDateTime

In the first case the DateAdd will be treated by CR (only once), the SQL pass will be something like yourdatefield> "03/07/2004 ..." (I have not CR at the moment)
Furthermore, the index search will be used on the server side

In the second case there is a risk that CR doesn't translate in the database sql language the DateAdd function, and if it succeeds, there will be no index search on the server as the field is inside a function. And also there will be a lot of calculation of the DateAdd function, one for each scanned record.

django
bug exterminator
tips'n tricks addict