CODEWITHSUNDEEP
mysql
Robert
Robert

Reputation: 10390

String and Numeric Literals in Date and Time Context

MySql states the following about date:

As a number in either YYYYMMDD or YYMMDD format, provided that the number makes sense as a date. For example, 19830905 and 830905 are interpreted as '1983-09-05'.

So I tried:

select date 19830905;

output: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '19830905' at line 1

How do I go about using this format?

Upvotes: 0

Views: 31

Answers (1)

C3roe
C3roe

Reputation: 96383

On the very same page you are quoting from, it says a bit further up:

Standard SQL and ODBC Date and Time Literals. Standard SQL permits temporal literals to be specified using a type keyword and a string. The space between the keyword and string is optional.
DATE 'str'
TIME 'str'
TIMESTAMP 'str'

So MySQL apparently allows SELECT DATE '19830905' to follow standard SQL – but using a numeric date value at this point simply seems not supported.

In other contexts however they work fine, such as f.e.

SELECT 19830905 + INTERVAL 1 DAY

(Result: 1983-09-06)

(What you quoted, is under the headline String and Numeric Literals in Date and Time Context. One could perhaps argue that by definition the case in question is not such a context, as it is explicitly defined differently, to accept a string literal only.)

Upvotes: 1

Related Questions