Formatting MySQL Dates



MySQL's DATE_FORMAT() function provides a wide range of format codes that you can use to represent date and time values in various formats. 

It allows you to format a date or datetime value as a string using custom formatting.

DATE_FORMAT(date, format)

date: The date or datetime value you want to format.

format: A string specifying the desired format for the output. This string can include special format codes that represent various components of the date and time (e.g., %Y for the year, %m for the month, %d for the day, %H for the hour, %i for the minute, %s for the second, etc.).

SELECT DATE_FORMAT('2023-09-03', '%M %d, %Y'); 
// returns 'September 03, 2023'.
Here is a list of commonly used format codes along with examples:
%Y: Four-digit year (e.g., '2023').
%y: Two-digit year (e.g., '23' for 2023).
%m: Month (01-12).
%c: Month (1-12).
%d: Day of the month (01-31).
%e: Day of the month (1-31).
%H: Hour (00-23).
%h: Hour (01-12).
%I: Hour (01-12).
%i: Minutes (00-59).
%s: Seconds (00-59).
%p: AM or PM.
%W: Weekday name (e.g., 'Monday').
%a: Abbreviated weekday name (e.g., 'Mon').
%M: Month name (e.g., 'September').
%b: Abbreviated month name (e.g., 'Sep').
%U: Week (00-53), where Sunday is the first day of the week.
%u: Week (01-53), where Monday is the first day of the week.
%V: Week (01-53), where Sunday is the first day of the week and the first week containing a Thursday is considered the first week.
%T: Time in 24-hour format ('HH:MM:SS').
%r: Time in 12-hour format ('hh:mm:ss AM/PM').
%D: Day of the month with ordinal suffix (e.g., '1st', '2nd', '3rd').
%j: Day of the year (001-366).
%X: Year for the week where Sunday is the first day of the week (e.g., '2023').
%x: Two-digit year for the week where Sunday is the first day of the week (e.g., '23' for 2023).
%%: A literal '%' character.
Here are some examples of how to use these format codes:
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d'); -- 2023-09-03
SELECT DATE_FORMAT(NOW(), '%H:%i:%s'); -- 14:30:00
SELECT DATE_FORMAT(NOW(), '%M %e, %Y'); -- September  3, 2023
SELECT DATE_FORMAT(NOW(), '%r'); -- 02:30:00 PM

conclusion

You can mix and match these format codes to create custom date and time representations that suit your specific needs. Additionally, you can include other characters (such as dashes, colons, and spaces) in the format string to format the output exactly as you want.

Comments

Post a Comment

Popular posts from this blog

Host Your Node.js App on AWS EC2 Instance for Free in 2024

Image
Contents Introduction Prerequisites Launch an EC2 Instance Prepare the EC2 Instance Connect to the EC2 Instance Install Dependencies and Start Your App in instance terminal Access Your App Run your server in auto mode using pm2 Conclusion 1. Introduction Are you a developer looking to host your Node.js app on a reliable and scalable platform? Amazon Web Services (AWS) offers a variety of services to meet your hosting needs, including EC2 instances. In this tutorial, we will guide you through the process of hosting your Node.js app on an AWS EC2 instance for free using the AWS Free Tier. 2. Prerequisites Before we begin, make sure you have the following prerequisites in place: An AWS account. If you don't have one, sign up for free at https://aws.amazon.com/. Basic knowledge of Node.js.  3. Launch an EC2 Instance 1.      Log in to the AWS Management Console. 2.      Navigate to the EC2 service. 3.      Click on "Launch Instance" to start the instance creation process . 4.
Read more

GitCommandsPro Your Guide to Essential Git Commands in 2024

Image
Table of Content 1 Check git version 2 Configuration Settings 3 Initializing a repository 4 Staging files 5 Viewing the status 6 Committing the staged files 7 Skipping the staging area 8. Removing files 9 Renaming or moving files 10 Viewing the staged/unstaged changes 11 Viewing the history 12 Viewing a commit 13 Unstaging files (undoing git add) 14 Discarding local changes 15 Restoring an earlier version of a file      1.     Check git version git --version 2.     Configuration Settings Levels of defining this settings are System level -  apply to All Users of computer. Global – all repositories of the current user. Local – the current repository. Or a repository in the current folder.           Name  git config --global user.name "YOUR_NAME"          Email git config --global user.email YOUR_MAIL@gmail.com          Default Editor (for setting vscode) git config --global core.editor "code --wait"           To edit all the global settings (will open the d
Read more

SOAP Explained: With Javascript

What is SOAP? SOAP is like a set of rules that helps different computer programs talk to each other. Imagine you have two friends who speak different languages. SOAP is like a universal translator that helps them understand each other. Now, when these programs talk, they use a special type of text called XML. It's like a format that's easy for both programs to read, like having a conversation in a language that both friends understand. So, SOAP is kind of like a language translator for computers, and XML is the language they use to communicate. This way, even if two programs are running on completely different systems, they can still understand each other and share information. SOAP Request A SOAP request consists of a SOAP envelope that contains the SOAP header and the SOAP body. Here's a basic example of what a simple XML request in SOAP might look. <?xml version="1.0" encoding="UTF-8"?> <soapenv:Envelope     xmlns:soapenv="http://schemas
Read more