[Solved] Syntax for using dates in querries

[bhilton]

I have been writing SQL in the Microsoft arena for 20 some years but the Base syntax is driving me nuts. I want to be able to select data from a table named "Transaction" based on the date of the transaction falling between 2 dates. I have even reduced it to a very simple test of the transaction date being less than a specified date as shown below, but keep getting syntax error. What am I missing?

SELECT "Symbol",SUM("NumberOfShares") "Shares"
FROM "Transaction" "t"
WHERE "t"."TransactionDate" < "'1/1/12'"
GROUP BY "t"."Symbol"

[Sliderule]

Use EITHER of the two Queries below:

1. Code: Select all

   SELECT "Symbol",SUM("NumberOfShares") "Shares"
   FROM "Transaction" "t"
   WHERE "t"."TransactionDate" < {D '2012-01-01'}
   GROUP BY "t"."Symbol"

2. Code: Select all

   SELECT "Symbol",SUM("NumberOfShares") "Shares"
   FROM "Transaction" "t"
   WHERE "t"."TransactionDate" < '2012-01-01'
   GROUP BY "t"."Symbol"

Explanation: ONLY table , view, or column names are surrounded by double quotes ( for example: "Transaction" ). Text string, date, time, timestamp are surounded by single quotes, for example: 'IBM' . These ARE the SQL standard, which, often, Microsoft / Access does NOT follow .

IF you are entering the Date, using a Query built with the Base Parser ( Create Query in Design View ) . . . you can enter the date on the Criterion line in any of the below formats, and, the Base Parser will convert it to the SQL date format you want:

1. YYYY-MM-DD example: 2012-01-01
2. MM-DD-YYYY example: 01-01-2012
3. MM/DD/YYYY example: 01/01/2012
4. MM-DD-YY example: 01-01-12
5. MM/DD/YY example: 01/01/12

I hope this helps, please be sure to let me / us know.

Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[bhilton]

Thanks for the reply. Yes, I had tried that - and every combination of single and double quotes with and without (). I just tried both formats you suggested and still get the following error when I try to run it.

SQL Status: HY000
Error code: 1000

syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE

If I comment the WHERE clause it runs clean. Driving me crazy!

Any other thoughts?

[Villeroy]

Download download/file.php?id=18682 which is an arbitrary example database of mine and play with the following equivalent queries:

Code: Select all

SELECT * FROM "Table1" WHERE "Date" >= '2009-01-01' AND "Date" <= '2009-03-31'

SELECT * FROM "Table1" WHERE "Date" BETWEEN '2009-01-01' AND '2009-03-31'

SELECT * FROM "Table1" WHERE Year("Date")=2009  AND Quarter("Date")=1

[Sliderule]

Are you writing the Query ( Select statement ) in a MACRO?

To see the REAL error ( syntax error ) returned by the database back-end engine ( HSQL ) . . . do NOT use the OpenOffice / LibreOffice Base Parser, that is, send the SQL directly to the database engine, and look at the error message.

To accomplish this, after writing the Select Statement, EITHER:

1. From the Toolbar, press the SQL icon ( Run SQL command directly )
2. From the Menu: Edit -> Run the SQL command directly

Also, please, display again here, the Select statement that does NOT work, so, I / we can review it.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[bhilton]

Here is a "cleaned up" statement that I am trying to use. (Eventually I will be passing dates from a form but I need to expand the statement and test before doing so.)

SELECT "Symbol",SUM("NumberOfShares") "Shares"
FROM "Transaction" "t"
WHERE "TransactionDate" BETWEEN '1/1/1995' AND '12/31/12'
GROUP BY "t"."Symbol"

The error message when running as SQL Direct is "The data content could not be loaded. Wrong data type java.lang.IllegalArgumentException in statement ..."

Here is the creation script for table "Transaction"

CREATE TABLE "Transaction" (
"TransactionID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0) NOT NULL PRIMARY KEY,
"Symbol" VARCHAR(8) NOT NULL,
"StrategyID" INTEGER NOT NULL,
"AccountID" INTEGER NOT NULL,
"TransactionDate" DATE NOT NULL,
"TransactionType" VARCHAR(12) NOT NULL,
"NumberOfShares" DECIMAL(12,6),
"CostPerShare" DECIMAL(12,6),
"Commission" DECIMAL(8,2),
"Fees" DECIMAL(8,2),
"PortfolioID" INTEGER NOT NULL,
CONSTRAINT FK_EQ_TRAN FOREIGN KEY ("Symbol") REFERENCES "Equity" ("Symbol"),
CONSTRAINT FK_ACC_TRAN FOREIGN KEY ("AccountID") REFERENCES "Account" ("AccountID"),
CONSTRAINT FK_STRAT_TRAN FOREIGN KEY ("StrategyID") REFERENCES "Strategy" ("StrategyID"),
CONSTRAINT FK_PORT_TRAN FOREIGN KEY ("PortfolioID") REFERENCES "Portfolio" ("PortfolioID")
);

Thanks for your help. Obviously, I am missing something very basic.

[Villeroy]

You are using invalid US dates such as '12/31/12'.

[Sliderule]

Code it as:

Code: Select all

SELECT "Symbol",SUM("NumberOfShares") "Shares"
FROM "Transaction" "t"
WHERE "TransactionDate" BETWEEN '1995-01-01' AND '2012-12-31'
GROUP BY "t"."Symbol"

Explanation: Dates are surrounded by single quotes, and, in YYYY-MM-DD format, for example: '1995-01-01'
I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[Sliderule]

Just as an FYI . . . if you want, in your Query, to be PROMPTED to input a date ranges . . . you could write the Query as:

Code: Select all

SELECT "Symbol",SUM("NumberOfShares") "Shares"
FROM "Transaction" "t"
WHERE "TransactionDate" BETWEEN :Enter_From_TransactionDate AND :Enter_To_TransactionDate
GROUP BY "t"."Symbol"

In the above, since, the OpenOffice / LibreOffice BASE PARSER will first 'edit' the query before sending it to the database back-end ( HSQL ) . . . you can enter the dates, for example, in, YYYY-MM-DD format, or, MM-DD-YYYY format, or, MM/DD/YY format etc, since, the BASE PARSER will know, depending on your location, whether, the date format to use ( day of month before month, or, month before day of month, etc ).

The use of :Enter_From_TransactionDate means, since it STARTS with a colon ( : ) it ( BASE PARSER ) will prompt, user to enter values, and, it will substitute the values user enters in the Query ( Select statement ) before sending it to the database engine.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[bhilton]

Thanks for all the replies. It was the format of the literal dates. Changed them to be like '2010-12-31' and all worked correctly.
Thanks again.