Writing Date and Time Literals in MySQL

Q

How To Write Date and Time Literals in MySQL?

✍: FYIcenter.com

A

MySQL offers a number of formats for you to use to enter date and time literals:

  • ANSI standard format: "YYYY-MM-DD HH:MM:SS".
  • Non-standard limiters. Like: "YYYY/MM/DD HH^MM^SS" or "YYYY.MM.DD HH-MM-SS".
  • No limiters. Like: "YYYYMMDD" for a date or "HHMMSS" for a time.
  • Decimal numbers. Like: 8 digits dddddddd for a date or 6 digits dddddd for a time.

The tutorial exercise below gives you some good examples:

SELECT DATE('1997-01-31') FROM DUAL;
   1997-01-31

SELECT DATE('1997-01-31 09:26:50') FROM DUAL;
   1997-01-31

SELECT TIME('1997-01-31 09:26:50') FROM DUAL;
   09:26:50

SELECT DATE('1997/01/31 09^26^50') FROM DUAL;
   1997-01-31

SELECT TIME('1997/01/31 09^26^50') FROM DUAL;
   09:26:50

SELECT DATE('19970131') FROM DUAL;
   1997-01-31

SELECT TIME('092650') FROM DUAL;
   09:26:50

SELECT DATE(19970131) FROM DUAL; -- Crazy format
   1997-01-31

SELECT TIME(092650) FROM DUAL; -- Crazy format
   09:26:50

 

Introduction to Date and Time Handling in MySQL

⇒⇒MySQL Database Tutorials

2016-10-17, 175👍, 0💬