Working with Dates and Times in MySQL – Part 4

Mar 22, 2022 by Robert Gravelle

In this this series on Dates and Times, we’ve explored MySQL’s five temporal data types, as well as some of its many date/time-oriented functions. In this installment, we’ll be covering a few ways to create dates and times in MySQL.

In Part 3, we took a brief look at the MAKEDATE() function. It takes a year and dayofyear and returns the resulting Date value. For instance, MAKEDATE(2021, 200) would return a Date of “2021-07-19”. The downside to this function should be readily obvious; it takes some calculation to determine the dayofyear if you have a year, month, and day. In that case, you can make a DATE by combining MAKEDATE() with DATE_ADD(). MAKEDATE() with a day of 1 will give you a DATE for the first day of the given year, and then you can add to it the month and day with DATE_ADD(). Here’s an example that sets the year and month only:

This SELECT statement includes the day as well:

If you’re looking to create a TIME only, MAKETIME() returns a time value calculated from the hour, minute, and second arguments:

The second argument can have a fractional part for milliseconds:

Another option for creating a DATE, TIME, or DATETIME is to use the STR_TO_DATE() function. It takes a date string and a format string and returns:

• a DATE value if the string contains only date
• a TIME value if the string contains only time
• a DATETIME value if the format string contains both date and time parts

Moreover, if the date, time, or datetime value extracted from str is invalid, STR_TO_DATE() returns NULL and produces a warning.

Some Examples

Here are a couple of Dates in Navicat for MySQL 16:

Scanning starts at the beginning of str and fails if format is found not to match. Meanwhile, extra characters at the end of str are ignored:

Unspecified date or time parts have a value of 0, so incompletely specified values in the date/time string produce a result with some or all parts set to 0:

For the full list of specifiers that can be used in format, see the DATE_FORMAT() function description in the official MySQL docs.

If we had two separate DATE and TIME values, we could get a DATETIME value by concatenating the the results of MAKEDATE() and MAKETIME() and then passing the combined string to STR_TO_DATE(). While that might sound like a lot of work, it’s really quite simple in practice:

In this installment of the Working with Dates and Times in MySQL series, we covered a few ways to create dates and times in MySQL using some of its specialized date and time functions. In the next installment, we’ll look at how to use temporal data in your SELECT queries.