dynamobi/dynamobi — Issue #378: [FRG-397] Improve error message when interval field value does not match precision
Author: dynamobi-build
Created: 2011-12-30 03:11:31+00:00
State: open
Labels: FRG, SQL Validator

[reporter="jhyde", created="Thu, 13 May 2010 15:11:11 -0500 (GMT-05:00)"]
SQL:2008 specification part 2, 4.6.3 Intervals says:

Within a value of type interval, the first field is constrained only by the of

the associated . Table 6, "Valid values for fields in INTERVAL values", specifies the constraints

on subsequent field values.

Thus:

* INTERVAL '90' SECOND is legal

* INTERVAL '120' SECOND is legal

* INTERVAL '86400' SECOND is legal

* INTERVAL '1:30' MINUTE TO SECOND is legal

* INTERVAL '1:90' SECOND is illegal

* INTERVAL '1:-30' SECOND is illegal

* INTERVAL '1:59.999' SECOND is illegal

* INTERVAL '1:59:59' HOUR TO SECOND is legal

* INTERVAL '1:59:61' HOUR TO SECOND is illegal

* INTERVAL '1:61:59' HOUR TO SECOND is illegal

In particular,

> VALUES INTERVAL '120' SECOND;

gives

> Error: from line 1, column 8 to line 1, column 28: Illegal interval literal format '120' for INTERVAL SECOND

but it should succeed.