IT Log

Record various IT issues and difficulties.

MySQL Practical Series: Date Formatting

In MySQL, you can use the DATE_FORMAT() function to format dates. The DATE_FORMAT() function is typically used to format fields of type DATETIME or TIMESTAMP. This function allows you to display dates and times in a specified format. Below are some examples of common date formatting:

  1. Show year-month-day:

  2. Show month/day/year:

    [/crayon]

  • Show full date and time:

  • Show hours and minutes:

  • Show day of the week:

    [/crayon]

  • Show the name of the month:

  • Show the abbreviation of the month:

    [/crayon]

  • Show the last two digits of the year:

    function. You can combine different formatting options as needed to create custom date formats.

    If you attempt to use DATE_FORMAT() on a non-date/time type field, such as INT or VARCHAR, MySQL will return an error because it cannot interpret these types of data as dates or times.

    If you have a non-date/time type field but know that it contains date or time information, you may need to first convert it to a DATETIME type before using the DATE_FORMAT() function. For example, if a VARCHAR field contains a datetime string, you can use the STR_TO_DATE() function to convert it:

    			<span class="token keyword">SELECT</span> DATE_FORMAT<span class="token punctuation">(</span>STR_TO_DATE<span class="token punctuation">(</span>your_varchar_column<span class="token punctuation">,</span> <span class="token string">'%Y-%m-%d %H:%i:%s'</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token string">'%Y-%m-%d %H:%i:%s'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>[/crayon]
    

    Here, the 
			STR_TO_DATE() function converts the string to a DATETIME type, and then the 
			DATE_FORMAT() function formats it to the desired format.
    • , , , , , , , , ,

    10 responses to “MySQL Practical Series: Date Formatting”

    1. The combination of functions explained here is exactly what I needed. This article has improved my SQL skills.

    2. A must-read for anyone working with dates in MySQL! It demystifies date formatting and provides actionable code snippets.

    3. Learning about DATE_FORMAT() and STR_TO_DATE() together was key. The examples made it easy to grasp.

    4. I appreciate the clear examples provided. This article has saved me time figuring out date formatting in SQL queries.

    5. This tutorial is concise yet comprehensive. It answers how to format dates in MySQL, which I needed for my current project.

    6. The use of STR_TO_DATE() function is cleverly demonstrated here. This will be helpful for those dealing with string date formats.

    7. I found this article extremely useful. It covers all the basic date formatting needs and even explains handling non-date fields.

    8. Thanks for sharing such a practical tutorial! The explanations are straightforward, making it easy to understand how to format dates in MySQL.

    9. The step-by-step guide on formatting dates and times is very helpful. I especially liked the examples showing different date formats.

    10. This article is a great resource for anyone looking to format dates in MySQL. It clearly explains how to use the DATE_FORMAT() function with various examples.

    Leave a Reply