Sql Developer Date Comparison-CodePudding

In code I have dates as ZonedDateTime (Java)

SELECT * 
FROM MYTABLE 
WHERE DATE_START = to_date('22/01/22 01:00:00', 'YYYY-MM-DD hh:mm:ss[.nnnnnnn]')      --(TIMESTAMP(6))
  AND DATE_TO = to_date('22/01/22 02:00:00', 'YYYY-MM-DD hh:mm:ss[.nnnnnnn]')     --(TIMESTAMP(6))

When I run this query I get:

inconsistent datatypes: expected %s got %s" Expected NUMBER, got DATE

When I try without to_date I get:

00000 - "invalid number"

When I try with to_char the same like as to_date, I have no idea where the problem is. How can I achieve this? Nothing works. My date format in column is e.q:

22/01/22 01:00:00,000000000

CodePudding user response：

One issue you have right now is you are asking for the year to be represented by a 4-year digit and then only giving it a 2 digit value. You will need to switch yyyy -> yy

CodePudding user response：

Since you have a 2-digit year, you want RR or YY instead of YYYY and HH24 for hours and MI for the minutes and, if you want optional fractional seconds then you can use TO_TIMESTAMP and .FF:

SELECT *
FROM   MYTABLE
WHERE  DATE_START = TO_TIMESTAMP('22/01/22 01:00:00', 'RR-MM-DD HH24:MI:SS.FF')
AND    DATE_TO    = TO_TIMESTAMP('22/01/22 02:00:00', 'RR-MM-DD HH24:MI:SS.FF')

Or, you can use TIMESTAMP literals:

SELECT *
FROM   MYTABLE
WHERE  DATE_START = TIMESTAMP '2022-01-22 01:00:00'
AND    DATE_TO    = TIMESTAMP '2022-01-22 02:00:00'

If you are calling it from Java and passing in the times then use:

SELECT *
FROM   MYTABLE
WHERE  DATE_START = ?
AND    DATE_TO    = ?

and pass in the timestamps as bind parameters.

Which, for the sample data:

CREATE TABLE mytable (date_start, date_to) AS
SELECT TIMESTAMP '2022-01-22 01:00:00.000000', TIMESTAMP '2022-01-22 02:00:00.000000' FROM DUAL;

Both output:

DATE_START DATE_TO

2022-01-22 01:00:00.000000000 2022-01-22 02:00:00.000000000

DATE_START	DATE_TO
2022-01-22 01:00:00.000000000	2022-01-22 02:00:00.000000000

db<>fiddle here