DATETIME

USAGE

DETAILS

SYNONYMS

SCHEMA

PARAMETERS

SKYSQL

PRIVILEGES

EXAMPLES

DATETIME Format

CREATE TABLE datetime_formats_example (
  description VARCHAR(30),
  example DATETIME(6)
);

-- The time zone has no effect on the values
SET @@time_zone = '+00:00';

INSERT INTO datetime_formats_example VALUES
  ('Date without time', '2019-12-30'),
  ('Full year', '2019-12-30 00:00:00'),
  ('Short year', '19-12-30 00:00:00.000'),
  ('Pipe delimiters', '19|2|3 19|00|00.123456'),
  ('Forward slash delimiter', '19/12/30 00/00/00.0');

SET @@time_zone = '-07:00';

INSERT INTO datetime_formats_example VALUES
  ('Asterisk delimiter', '19*12*30 8*35*00'),
  ('Comma delimiter', '19,2,3 12,34,56.123');

The resulting data would look like this:

SELECT * FROM datetime_formats_example;

+-------------------------+----------------------------+
| description             | example                    |
+-------------------------+----------------------------+
| Date without time       | 2019-12-30 00:00:00.000000 |
| Full year               | 2019-12-30 00:00:00.000000 |
| Short year              | 2019-12-30 00:00:00.000000 |
| Pipe delimiters         | 2019-02-03 19:00:00.123456 |
| Forward slash delimiter | 2019-12-30 00:00:00.000000 |
| Asterisk delimiter      | 2019-12-30 08:35:00.000000 |
| Comma delimiter         | 2019-02-03 12:34:56.123000 |
+-------------------------+----------------------------+

The default microsecond precision when unspecified is 0, and you can use that in a cast in order to trim off stored microseconds:

SELECT description, CONVERT(example, DATETIME) AS example
  FROM datetime_formats_example;

+-------------------------+---------------------+
| description             | example             |
+-------------------------+---------------------+
| Date without time       | 2019-12-30 00:00:00 |
| Full year               | 2019-12-30 00:00:00 |
| Short year              | 2019-12-30 00:00:00 |
| Pipe delimiters         | 2019-02-03 19:00:00 |
| Forward slash delimiter | 2019-12-30 00:00:00 |
| Asterisk delimiter      | 2019-12-30 08:35:00 |
| Comma delimiter         | 2019-02-03 12:34:56 |
+-------------------------+---------------------+

ERROR HANDLING

FEATURE INTERACTION

RESPONSES

DIAGNOSIS

ISO 9075:2016

CHANGE HISTORY

Release Series

History

10.6 Enterprise

  • Present starting in MariaDB Enterprise Server 10.6.4-1.

10.6 Community

  • Present starting in MariaDB Community Server 10.6.0.

10.5 Enterprise

  • Present starting in MariaDB Enterprise Server 10.5.3-1.

10.5 Community

  • Present starting in MariaDB Community Server 10.5.0.

10.4 Enterprise

  • Present starting in MariaDB Enterprise Server 10.4.6-1.

10.4 Community

  • Present starting in MariaDB Community Server 10.4.0.

10.3 Enterprise

  • Present starting in MariaDB Enterprise Server 10.3.16-1.

10.3 Community

  • Present starting in MariaDB Community Server 10.3.0.

10.2 Enterprise

  • Present starting in MariaDB Enterprise Server 10.2.25-1.

10.2 Community

  • Present starting in MariaDB Community Server 10.2.0.

Release Series

History

10.6 Enterprise

  • Present starting in MariaDB Enterprise Server 10.6.4-1.

10.5 Enterprise

  • Present starting in MariaDB Enterprise Server 10.5.3-1.

10.4 Enterprise

  • Present starting in MariaDB Enterprise Server 10.4.6-1.

EXTERNAL REFERENCES