[Solved] Query of dates within a year of each other?

[Neotone]

I'm trying to design a query which selects entries in a table where the difference between one date and another date is less than a year. I.e. Date1-Date2 <= 1 year. How do I do this?

[QuazzieEvil]

Use the DateDiff function

DateDiff('yy',"Date1","Date2")

Note that the yy single quoted.

in a query, use this function the the FIELDS row of the query design, and use what ever criterion as with any column.

[Neotone]

Okay, here's my code:

Code: Select all

SELECT "AssetID", "StatusID", "AssetDescription", "BuyDate", "BuyPrice", "SellDate", "SellPrice", "CostofSale", "ProceedsfromSale" FROM "Date_Range_CapitalGainsandLosses" AS "Date_Range_CapitalGainsandLosses" WHERE ( DATEDIFF ( 'yy', "BuyDate", "SellDate" ) <= 01)

And here's the error message I get:

The data content could not be loaded.

Table not found in statement [SELECT "AssetID", "StatusID", "AssetDescription", "BuyDate", "BuyPrice", "SellDate", "SellPrice", "CostofSale", "ProceedsfromSale" FROM "Date_Range_CapitalGainsandLosses" AS "Date_Range_CapitalGainsandLosses" WHERE ( DATEDIFF ( 'yy', "BuyDate", "SellDate" ) <= 01)]

What am I doing wrong?

[QuazzieEvil]

have you double checked that you spelled the table name correctly?

[Neotone]

Well there was one problem... the query was trying to select an element from the table that wasn't there. But I fixed that and still got the same error message.

I've tried getting rid of the quotes around 01, changing them to double quotes, dropping the zero, changing 'yy' to double quotes, and every possible combination thereof and nothing worked. I wish I had some example code utilizing the DATEDIFF function so I could be sure I'm formatting the thing right.

[pjwalkerpj]

I have used the DATEDIFF function in a slightly different way but it may give you a clue as to a syntax that works.

I have two tables one containing a start time eg 9:00am and the other table contains the finish time eg 10:00am.

Application of DATEDIFF( "MINUTE", "StartTime", "FinishTime") returns the value 60.
In your case I would try changing the word MINUTE to YEAR or MONTH or DAY depending on what you are after and see what happens.
I hope this helps

Peter J Walker
Practising Professional Engineer

[Neotone]

Okay, in accordance with your idea, I changed the code to the following:

Code: Select all

SELECT "AssetID", "AssetDescription", "BuyDate", "BuyPrice", "SellDate", "SellPrice", "CostofSale", "ProceedsfromSale" FROM "Date_Range_CapitalGainsandLosses" AS "Date_Range_CapitalGainsandLosses" WHERE ( DATEDIFF ( "YEAR", "BuyDate", "SellDate" ) <= 1 )

It still didn't work. Same error.

[Villeroy]

There is no database object "YEAR" and the function does not expect any database object. It expects a literal string, such as 'YEAR'.
Wrong:
DateDiff ("YEAR" , "field 1", "field 2")
Correct:
DateDiff ('YEAR' , "field 1", "field 2")

Literal strings in quotes, object names (field, table, alias) in double-quotes, numbers without any quotes.

[Neotone]

Okay... I changed the code to:

Code: Select all

SELECT "AssetID", "AssetDescription", "BuyDate", "BuyPrice", "SellDate", "SellPrice", "CostofSale", "ProceedsfromSale" FROM "Date_Range_CapitalGainsandLosses" AS "Date_Range_CapitalGainsandLosses" WHERE ( DATEDIFF ( 'YEAR', "BuyDate", "SellDate" ) <= 1 )

Still doesn't work.

[Villeroy]

The data content could not be loaded.

Table not found in statement [SELECT "AssetID", "StatusID", "AssetDescription", "BuyDate", "BuyPrice", "SellDate", "SellPrice", "CostofSale", "ProceedsfromSale" FROM "Date_Range_CapitalGainsandLosses" AS "Date_Range_CapitalGainsandLosses" WHERE ( DATEDIFF ( 'yy', "BuyDate", "SellDate" ) <= 01)]

There is no table with exact name "Date_Range_CapitalGainsandLosses" in your database?

"Date_Range_CapitalGainsandLosses" can be a query as well, but then you need to turn off menu:Edit>Run SQL directly... because the underlying HSQL database does not see any queries that have been defined in some frontend tool such as Base. "Direct SQL" passes over the SELECT string over to the database engine which knows about it's tables and views, but not the queries defined in Base.
Yes, you are working with 2 separate applications at the same time while using Base.

[Neotone]

By all appearances, that worked. At least I get no error messages. If it didn't work and I need more help, I'll come back here.

Thank you!