Home >> FAQs/Tutorials >> MySQL Tutorials

MySQL Tutorial - Writing Date and Time Literals

By: FYIcenter.com

(Continued from previous topic...)

How To Write Date and Time Literals?

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

(Continued on next topic...)

  1. What Are Date and Time Data Types?
  2. How To Write Date and Time Literals?
  3. How To Enter Microseconds in SQL Statements?
  4. How To Convert Dates to Character Strings?
  5. How To Convert Character Strings to Dates?
  6. What Are Date and Time Intervals?
  7. How To Increment Dates by 1?
  8. How To Decrement Dates by 1?
  9. How To Calculate the Difference between Two Dates?
  10. How To Calculate the Difference between Two Time Values?
  11. How To Present a Past Time in Hours, Minutes and Seconds?
  12. How To Extract a Unit Value from a Date and Time?
  13. What Are Date and Time Functions?
  14. What Is TIMESTAMP Data Type?
  15. How Many Ways to Get the Current Time?

MySQL Tutorials:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...