The DATE_FORMAT() function formats a date as specified.
DATE_FORMAT(date, format)
Format Description
%a Abbreviated weekday name (Sun to Sat)
%b Abbreviated month name (Jan to Dec)
%c Numeric month name (0 to 12)
%D Day of the month as a numeric value, followed by suffix (1st, 2nd, 3rd, ...)
%d Day of the month as a numeric value (08 to 31)
%e Day of the month as a numeric value (8 to 31)
%f Microseconds (000000 to 999999)
%H Hour (00 to 23)
%h Hour (00 to 12)
%I Hour (00 to 12)
%i Minutes (00 to 59)
%j Day of the year (001 to 366)
%k Hour (0 to 23)
%l Hour (1 to 12)
%M Month name in full (January to December)
%m Month name as a numeric value (00 to 12)
%p AM or PM
%r Time in 12 hour AM or PM format (hh:mm:ss AM/PM)
%S Seconds (00 to 59)
%s Seconds (00 to 59)
%T Time in 24 hour format (hh:mm:ss)
%U Week where Sunday is the first day of the week (00 to 53)
%u Week where Monday is the first day of the week (00 to 53)
%V Week where Sunday is the first day of the week (01 to 53). Used with %X
%v Week where Monday is the first day of the week (01 to 53). Used with %x
%W Weekday name in full (Sunday to Saturday)
%w Day of the week where Sunday=0 and Saturday=6
%X Year for the week where Sunday is the first day of the week. Used with %V
%x Year for the week where Monday is the first day of the week. Used with %v
%Y Year as a numeric, 4-digit value
%y Year as a numeric, 2-digit value
-------------
SELECT DATE_FORMAT("2021-06-04", "%W %M %e %Y");
SELECT DATE_FORMAT("2021-06-04", "%M %d %Y");
to day is monday 2nd janu 2021 week num is 23
SELECT DATE_FORMAT("2021-06-04", " today is %W %D %M %Y week num is %V");
===============================
Setting environment for using XAMPP for Windows.
Mounika@MOUNIKA-PC c:\xampp
# ram.cmd
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.4.18-MariaDB mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use vlrinst;
Database changed
MariaDB [vlrinst]> selct * from vlr;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'selct * from vlr' at line 1
MariaDB [vlrinst]> select * from vlr;
+-------------------------------------+---------------------+---------------------+
| comment | created_at | changed_at |
+-------------------------------------+---------------------+---------------------+
| Please save my number +919059868766 | 2021-05-26 22:32:47 | 2021-05-26 22:36:50 |
| please subcribe vlr | 2021-05-26 22:36:01 | 2021-05-26 22:36:01 |
+-------------------------------------+---------------------+---------------------+
2 rows in set (0.094 sec)
MariaDB [vlrinst]> select date_format(created_at,%a),created_at from vlr;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '%a),created_at from vlr' at line
1
MariaDB [vlrinst]> select date_format(created_at,"%a"),created_at from vlr;
+------------------------------+---------------------+
| date_format(created_at,"%a") | created_at |
+------------------------------+---------------------+
| Wed | 2021-05-26 22:32:47 |
| Wed | 2021-05-26 22:36:01 |
+------------------------------+---------------------+
2 rows in set (0.044 sec)
MariaDB [vlrinst]> select date_format(created_at,"%a"),created_at from vlr;
+------------------------------+---------------------+
| date_format(created_at,"%a") | created_at |
+------------------------------+---------------------+
| Wed | 2021-05-26 22:32:47 |
| Wed | 2021-05-26 22:36:01 |
+------------------------------+---------------------+
2 rows in set (0.001 sec)
MariaDB [vlrinst]> select date_format(created_at,"%a,%b"),created_at from vlr;
+---------------------------------+---------------------+
| date_format(created_at,"%a,%b") | created_at |
+---------------------------------+---------------------+
| Wed,May | 2021-05-26 22:32:47 |
| Wed,May | 2021-05-26 22:36:01 |
+---------------------------------+---------------------+
2 rows in set (0.001 sec)
MariaDB [vlrinst]> select date_format(created_at,"%a,%b,%c"),created_at from vlr;
+------------------------------------+---------------------+
| date_format(created_at,"%a,%b,%c") | created_at |
+------------------------------------+---------------------+
| Wed,May,5 | 2021-05-26 22:32:47 |
| Wed,May,5 | 2021-05-26 22:36:01 |
+------------------------------------+---------------------+
2 rows in set (0.000 sec)
MariaDB [vlrinst]> select date_format(created_at,"%a,%b,%c,%D"),created_at from vlr;
+---------------------------------------+---------------------+
| date_format(created_at,"%a,%b,%c,%D") | created_at |
+---------------------------------------+---------------------+
| Wed,May,5,26th | 2021-05-26 22:32:47 |
| Wed,May,5,26th | 2021-05-26 22:36:01 |
+---------------------------------------+---------------------+
2 rows in set (0.003 sec)
MariaDB [vlrinst]> select date_format(created_at,"%a,%b,%c,%D,%e"),created_at from vlr;
+------------------------------------------+---------------------+
| date_format(created_at,"%a,%b,%c,%D,%e") | created_at |
+------------------------------------------+---------------------+
| Wed,May,5,26th,26 | 2021-05-26 22:32:47 |
| Wed,May,5,26th,26 | 2021-05-26 22:36:01 |
+------------------------------------------+---------------------+
2 rows in set (0.001 sec)
MariaDB [vlrinst]> select date_format(created_at,"%i,%e"),created_at from vlr;
+---------------------------------+---------------------+
| date_format(created_at,"%i,%e") | created_at |
+---------------------------------+---------------------+
| 32,26 | 2021-05-26 22:32:47 |
| 36,26 | 2021-05-26 22:36:01 |
+---------------------------------+---------------------+
2 rows in set (0.002 sec)
MariaDB [vlrinst]> select date_format(created_at,"%J"),created_at from vlr;
+------------------------------+---------------------+
| date_format(created_at,"%J") | created_at |
+------------------------------+---------------------+
| J | 2021-05-26 22:32:47 |
| J | 2021-05-26 22:36:01 |
+------------------------------+---------------------+
2 rows in set (0.001 sec)
MariaDB [vlrinst]> select date_format(created_at,"%j"),created_at from vlr;
+------------------------------+---------------------+
| date_format(created_at,"%j") | created_at |
+------------------------------+---------------------+
| 146 | 2021-05-26 22:32:47 |
| 146 | 2021-05-26 22:36:01 |
+------------------------------+---------------------+
2 rows in set (0.001 sec)
MariaDB [vlrinst]> select date_format("2020-12-31","%j"),created_at from vlr;
+--------------------------------+---------------------+
| date_format("2020-12-31","%j") | created_at |
+--------------------------------+---------------------+
| 366 | 2021-05-26 22:32:47 |
| 366 | 2021-05-26 22:36:01 |
+--------------------------------+---------------------+
2 rows in set (0.001 sec)
MariaDB [vlrinst]> select date_format("2019-12-31","%j"),created_at from vlr;
+--------------------------------+---------------------+
| date_format("2019-12-31","%j") | created_at |
+--------------------------------+---------------------+
| 365 | 2021-05-26 22:32:47 |
| 365 | 2021-05-26 22:36:01 |
+--------------------------------+---------------------+
2 rows in set (0.001 sec)
MariaDB [vlrinst]> select date_format("2019-12-31","%M"),created_at from vlr;
+--------------------------------+---------------------+
| date_format("2019-12-31","%M") | created_at |
+--------------------------------+---------------------+
| December | 2021-05-26 22:32:47 |
| December | 2021-05-26 22:36:01 |
+--------------------------------+---------------------+
2 rows in set (0.001 sec)
MariaDB [vlrinst]> select date_format("2019-12-31","%p"),created_at from vlr;
+--------------------------------+---------------------+
| date_format("2019-12-31","%p") | created_at |
+--------------------------------+---------------------+
| AM | 2021-05-26 22:32:47 |
| AM | 2021-05-26 22:36:01 |
+--------------------------------+---------------------+
2 rows in set (0.000 sec)
MariaDB [vlrinst]> select date_format(created_at,"%p"),created_at from vlr;
+------------------------------+---------------------+
| date_format(created_at,"%p") | created_at |
+------------------------------+---------------------+
| PM | 2021-05-26 22:32:47 |
| PM | 2021-05-26 22:36:01 |
+------------------------------+---------------------+
2 rows in set (0.001 sec)
MariaDB [vlrinst]> select date_format(created_at,"%U"),created_at from vlr;
+------------------------------+---------------------+
| date_format(created_at,"%U") | created_at |
+------------------------------+---------------------+
| 21 | 2021-05-26 22:32:47 |
| 21 | 2021-05-26 22:36:01 |
+------------------------------+---------------------+
2 rows in set (0.001 sec)
MariaDB [vlrinst]> select date_format("2019-12-31","%U"),created_at from vlr;
+--------------------------------+---------------------+
| date_format("2019-12-31","%U") | created_at |
+--------------------------------+---------------------+
| 52 | 2021-05-26 22:32:47 |
| 52 | 2021-05-26 22:36:01 |
+--------------------------------+---------------------+
2 rows in set (0.001 sec)
MariaDB [vlrinst]> select date_format("2019-12-31","%y"),created_at from vlr;
+--------------------------------+---------------------+
| date_format("2019-12-31","%y") | created_at |
+--------------------------------+---------------------+
| 19 | 2021-05-26 22:32:47 |
| 19 | 2021-05-26 22:36:01 |
+--------------------------------+---------------------+
2 rows in set (0.001 sec)
MariaDB [vlrinst]> SELECT DATE_FORMAT("2021-06-04", " today is %W %D %M %Y week num is %V");
+-------------------------------------------------------------------+
| DATE_FORMAT("2021-06-04", " today is %W %D %M %Y week num is %V") |
+-------------------------------------------------------------------+
| today is Friday 4th June 2021 week num is 22 |
+-------------------------------------------------------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]>
No comments:
Post a Comment
Note: only a member of this blog may post a comment.