Hey, folks! In this article, we will be focusing on an interesting topic — SQL date_format() function.
SQL comprises of many in-built functions to deal with the date and time values and manipulate the same. One such function is date_format() function.
So, let us begin!!
What is SQL date_format() function?
SQL date_format() function enables the database administrator to format the datetime values in a variety of forms and represent the same.
Here, the format specifies the different forms within which the date can be displayed to the users.
So, let us now focus on the structure of date_format() function in the upcoming section.
Syntax of SQL date_format() function
Have a look at the below syntax!
|
1 |
date_format(date, format); |
The date_format() function takes two values as input arguments–
- date
- format: The way or form in which we want to display the date values.
Now, let us have a look at the various formats with which the date can be represented–
| Format | Description |
|---|---|
| %a | Weekdays [Sun…Sat] |
| %b | Months [Jan..Dec] |
| %c | Months in numeric format [0..12] |
| %D | Days of the month [0th, 1st, 2nd, 3rd, etc] |
| %d | Days of the month in numeric format [00..31] |
| %e | Days of the month in numeric format [0..31] |
| %f | Microseconds |
| %H | Hour value in 24 hr-format (00..23) |
| %h | Hour value in 12 hr-format (01..12) |
| %I | Hour value (01..12) |
| %i | Minutes value in a numeric form (00..59) |
| %j | Days (001..366) |
| %k | Hour values (0..23) |
| %l | Hour values (1..12) |
| %M | Names of the month (January..December) |
| %m | Months in numeric format (00..12) |
| %p | AM/PM |
| %r | Time in a 12 hr-format |
| %S | Seconds value (00..59) |
| %s | Seconds value (00..59) |
| %T | Time in a 24-hour |
| %U | Week values (00-53), Sunday being the first day of the week |
| %u | Week values (00-53), Monday being the first day of the week |
| %V | Week values (01..53), Sunday being the first day of the week |
| %v | Week (01..53), Monday being the first day of the week |
| %W | Weekdays names |
| %w | Days of the week |
| %X | Year for the particular week, Sunday being the first day of the week |
| %x | Year for the particular week, Monday being the first day of the week |
| %Y | Year value in 4 digit numeric format |
| %y | Year value in 2 digit numeric format |
| %% | “%” character |
Examples of date_format() function
In the below example, we have formatted the current date value obtained using SQL NOW() function as follows– Date-Month(in words)-Year(YYYY)
|
1 |
Select DATE_FORMAT(NOW(),'%D-%M-%Y') |
Output:
|
1 2 |
DATE_FORMAT(NOW(),'%D-%M-%Y') 29th-August-2020 |
Now, we have displayed the current date values in numeric format as shown–
|
1 |
Select DATE_FORMAT(NOW(),'%d-%m-%y') |
Output:
|
1 2 |
DATE_FORMAT(NOW(),'%d-%m-%y') 29-08-20 |
In this example, we have formatted and displayed the weekday along with the date and month as follows–
|
1 |
Select DATE_FORMAT(NOW(),'%W:%D-%M') |
Output:
|
1 2 |
DATE_FORMAT(NOW(),'%W:%D-%M') Saturday:29th-August |
Below, we have displayed the current system’s date value in the below format–
Hour:Minutes–Month,Year(YYYY)
|
1 |
Select DATE_FORMAT(NOW(),'%H:%i--%b,%Y') |
Output:
|
1 2 |
DATE_FORMAT(NOW(),'%H:%i--%b,%Y') 17:00--Aug,2020 |
Conclusion
By this, we have come to the end of this topic. Feel free to comment below, in case you come across any question.
Till then, Stay tuned @ SQL with JournalDev and Keep Learning!!