Oracle RDB Dates (VMS DATES)
Hi Everyone,
I've get an issue with Oracle RDB dates. The database is running on a VMS system and the column data type is "DATE VMS".
Issue below:
EES RDB timestamp selection problem
The query :
SELECT
UPDATE_DT
FROM
table_name
WHERE
UPDATE_DT <= CAST('2004052722033830' AS DATE VMS)
AND
UPDATE_DT >= CAST('2004052722033800' AS DATE VMS)
Correctly returns :
2004-05-27 22:03:38.03
2004-05-27 22:03:38.09
2004-05-27 22:03:38.15
2004-05-27 22:03:38.21
2004-05-27 22:03:38.27
But :
SELECT
UPDATE_DT
FROM
table_name
WHERE
UPDATE_DT <= CAST('2004052722033830' AS DATE VMS)
AND
UPDATE_DT > CAST('2004052722033803' AS DATE VMS)
Returns one row too many :
2004-05-27 22:03:38.03
2004-05-27 22:03:38.09
2004-05-27 22:03:38.15
2004-05-27 22:03:38.21
2004-05-27 22:03:38.27
The first row, (2004-05-27 22:03:38.03) should not be returned as it is NOT GREATER THAN itself.
However, the LESS THAN operator appears to work correctly :
The query :
SELECT
UPDATE_DT
FROM
table_name
WHERE
UPDATE_DT < CAST('2004052722033827' AS DATE VMS)
AND
UPDATE_DT > CAST('2004052722033804' AS DATE VMS)
Correctly returns the following :
2004-05-27 22:03:38.09
2004-05-27 22:03:38.15
2004-05-27 22:03:38.21