Correct syntax for evaluating date or time in SQL

[r_avital]

Hi,
Version: 4.2.8.2
Build ID: 420m0(Build:2) on Linux

Using the embedded SQL engine
I have a table in Base, with several columns, one is defined as Time and has data in the format hh:mm:ss. I’m trying to write a query that will add a calculated column with the values 1, 2, 3, or 4, depending on the time in the time-defined column (say 1 am to 5 am, 5 to noon, noon to 6 pm 6 to 1 am). For now, I’m just testing a single case, and here’s the syntax I’m using:

SELECT “key”, “day”, “TOD”, “value” , CASEWHEN (“TOD” Between “01:00:00” and “05:00:00”, 1, 0) FROM “tblMeterReading”

Here’s the error I’m getting:

SQL Status: HY000
Error code: 1000

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

I’m pretty sure this has to do with how I enter the time values, but I’ve tried single quotes, hash, no quotes, double quotes, and the error is the same.

What am I missing? Sounds like it should be simple enough… Thanks in advance!

UPDATE:
I’ve just tried the following:

SELECT “key”, “day”, “TOD”, “value” , CASEWHEN (“TOD” BETWEEN Time(“01:00:00”) AND Time(“05:00:00”), “1”, “0”) AS “Order” FROM “tblMeterReading”

Same error.

I know I’m stuck on something really basic, and I can’t figure it out. Your help is appreciated.

[Ratslinger]

CASEWHEN ("TOD" Between '01:00:00' and '05:00:00', 1, 0)

When using dates make sure the format is 'YYYY-MM-DD' or `MYDATE = ‘2016-05-04’

Sorry - you probably need to enter this in SQL view & turn on Run SQL command directly on toolbar button (on/off toggle)

TimeSQL.odb

[r_avital]

Okay, thanks, but I’m trying to evaluate the time, not the date. The column data type is time. Any ideas on this? Thanks in advance.

[r_avital]

Yes, I’m entering this in SQL View, and I toggled “Run SQl command directly” as you suggested, but still the same error. Doesn’t that command apply when I try to pass the query through to a server? I’m not using a server, I’m using the embedded HSQL engine…

[Ratslinger]

Look at the very first line - this is your CASEWHEN statement with the time! Just didn’t post the entire SQL statement. All it is - replace quotes as shown. Added the date because it was in your question.

[r_avital]

Sorry for the misunderstanding. Working fine now. Thanks again!

Correct Syntax for test case:

SELECT “key”, “day”, “TOD”, “value” ,
CASEWHEN (“TOD” Between ‘01:00:00’ and ‘05:00:00’, 1, 0)
AS “TODNum”
FROM “tblMeterReading”

[r_avital]

Correct syntax for the functionality I was looking for:

SELECT “key”, “day”, “TOD”, “value” ,
CASEWHEN (“TOD” Between ‘00:00:00’ and ‘04:59:59’, 4,
CASEWHEN (“TOD” Between ‘05:00:00’ and ‘10:59:59’, 1,
CASEWHEN (“TOD” Between ‘11:00:00’ and ‘17:59:59’, 2,
CASEWHEN(“TOD” Between ‘18:00:00’ and ‘23:59:59’, 3,0
)
)
)
) AS “TODNum” FROM “tblMeterReading”