SQL date_format() function – All you need to know!

Filed Under: SQL
SQL Date Format() Function

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!

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–

FormatDescription
%aWeekdays [Sun…Sat]
%bMonths [Jan..Dec]
%cMonths in numeric format [0..12]
%DDays of the month [0th, 1st, 2nd, 3rd, etc]
%dDays of the month in numeric format [00..31]
%eDays of the month in numeric format [0..31]
%fMicroseconds
%HHour value in 24 hr-format (00..23)
%hHour value in 12 hr-format (01..12)
%IHour value (01..12)
%iMinutes value in a numeric form (00..59)
%jDays (001..366)
%kHour values (0..23)
%lHour values (1..12)
%MNames of the month (January..December)
%mMonths in numeric format (00..12)
%pAM/PM
%rTime in a 12 hr-format
%SSeconds value (00..59)
%sSeconds value (00..59)
%TTime in a 24-hour
%UWeek values (00-53), Sunday being the first day of the week
%uWeek values (00-53), Monday being the first day of the week
%VWeek values (01..53), Sunday being the first day of the week
%vWeek (01..53), Monday being the first day of the week
%WWeekdays names
%wDays of the week
%XYear for the particular week, Sunday being the first day of the week
%xYear for the particular week, Monday being the first day of the week
%YYear value in 4 digit numeric format
%yYear 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)

Select DATE_FORMAT(NOW(),'%D-%M-%Y')

Output:

DATE_FORMAT(NOW(),'%D-%M-%Y')
29th-August-2020

Now, we have displayed the current date values in numeric format as shown–

Select DATE_FORMAT(NOW(),'%d-%m-%y')

Output:

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–

Select DATE_FORMAT(NOW(),'%W:%D-%M')

Output:

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)

Select DATE_FORMAT(NOW(),'%H:%i--%b,%Y')

Output:

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!!

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages