We provide Seo,wordpress,digital marketing,pythan,go programming,c,c++,Php with Project,php laravel With project many More courses .
Translate
Saturday, 9 October 2021
PERIOD ADD,QUARTER,STR TO DATE Date functions sql videos in telugu 53
------------------------------------------------------------
MySQL SYSDATE() Function
SELECT SYSDATE();
The SYSDATE() function returns the current date and time.
Note: The date and time is returned as "YYYY-MM-DD HH:MM:SS" (string) or as YYYYMMDDHHMMSS (numeric)
-------------
SELECT TIME("2021-06-06 19:30:10");
--------
The TIME_FORMAT() function formats a time by a specified format.
TIME_FORMAT(time, format)
Format Description
%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)
%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)
SELECT TIME_FORMAT("19:30:10", "%h %i %s %p");
SELECT TIME_FORMAT("19:30:10", "%r");
SELECT TIME_FORMAT("19:30:10", "%T");
------------------
The TIME_TO_SEC() function converts a time value into seconds.
SELECT TIME_TO_SEC("03:30:00.999999");
SELECT TIME_TO_SEC("-03:30:00");
-----------
The TIMEDIFF() function returns the difference between two time/datetime expressions.
Note: time1 and time2 should be in the same format, and the calculation is time1 - time2.
SELECT TIMEDIFF("2021-06-06 13:15:11", "2021-06-06 13:10:10");
------------
The TIMESTAMP() function returns a datetime value based on a date or datetime value.
Note: If there are specified two arguments with this function, it first adds the second argument to the first, and then returns a datetime value.
SELECT TIMESTAMP("2017-07-23", "13:10:11");
SELECT TIMESTAMP("2017-07-23", "13:10:11");
-----------------
The TO_DAYS() function returns the number of days between a date and year 0 (date "0000-00-00").
The TO_DAYS() function can be used only with dates within the Gregorian calendar.
SELECT TO_DAYS("0001-06-06 09:34:00");
-------------
The WEEK() function returns the week number for a given date (a number from 0 to 53).
--------
The WEEKDAY() function returns the weekday number for a given date.
Note: 0 = Monday, 1 = Tuesday, 2 = Wednesday, 3 = Thursday, 4 = Friday, 5 = Saturday, 6 = Sunday.
SELECT WEEKDAY(CURDATE());
---------------
The WEEKOFYEAR() function returns the week number for a given date (a number from 1 to 53).
Note: This function assumes that the first day of the week is Monday and the first week of the year has more than 3 days.
------------
The YEAR() function returns the year part for a given date (a number from 1000 to 9999).
---------
The YEARWEEK() function returns the year and week number (a number from 0 to 53) for a given date.
YEARWEEK(date, firstdayofweek)
date Required. The date or datetime value to extract the year and week number from
firstdayofweek
Optional. Specifies what day the week starts on. Can be one of the following:
0 - First day of week is Sunday
1 - First day of week is Monday and the first week has more than 3 days
2 - First day of week is Sunday
3 - First day of week is Monday and the first week has more than 3 days
4 - First day of week is Sunday and the first week has more than 3 days
5 - First day of week is Monday
6 - First day of week is Sunday and the first week has more than 3 days
7 - First day of week is Monday
-----------------
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]> select sysdate();
+---------------------+
| sysdate() |
+---------------------+
| 2021-06-08 21:19:21 |
+---------------------+
1 row in set (0.047 sec)
MariaDB [vlrinst]> select time();
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 ')' at line 1
MariaDB [vlrinst]> select sysdate();
+---------------------+
| sysdate() |
+---------------------+
| 2021-06-08 21:20:32 |
+---------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT TIME("2021-06-06 19:30:10");
+-----------------------------+
| TIME("2021-06-06 19:30:10") |
+-----------------------------+
| 19:30:10 |
+-----------------------------+
1 row in set (0.016 sec)
MariaDB [vlrinst]> SELECT TIME(now());
+-------------+
| TIME(now()) |
+-------------+
| 21:21:00 |
+-------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT TIME_FORMAT("19:30:10", "%h %i %s %p");
+----------------------------------------+
| TIME_FORMAT("19:30:10", "%h %i %s %p") |
+----------------------------------------+
| 07 30 10 PM |
+----------------------------------------+
1 row in set (0.020 sec)
MariaDB [vlrinst]> SELECT TIME_FORMAT(now(), "%h %i %s %p");
+-----------------------------------+
| TIME_FORMAT(now(), "%h %i %s %p") |
+-----------------------------------+
| 09 24 50 PM |
+-----------------------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT now(),TIME_FORMAT(now(), "%h %i %s %p");
+---------------------+-----------------------------------+
| now() | TIME_FORMAT(now(), "%h %i %s %p") |
+---------------------+-----------------------------------+
| 2021-06-08 21:25:09 | 09 25 09 PM |
+---------------------+-----------------------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT now(),TIME_FORMAT(now(), "%r");
+---------------------+--------------------------+
| now() | TIME_FORMAT(now(), "%r") |
+---------------------+--------------------------+
| 2021-06-08 21:26:16 | 09:26:16 PM |
+---------------------+--------------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT now(),TIME_FORMAT(now(), "%T");
+---------------------+--------------------------+
| now() | TIME_FORMAT(now(), "%T") |
+---------------------+--------------------------+
| 2021-06-08 21:26:45 | 21:26:45 |
+---------------------+--------------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT TIME_TO_SEC("03:30:00.999999");
+--------------------------------+
| TIME_TO_SEC("03:30:00.999999") |
+--------------------------------+
| 12600.999999 |
+--------------------------------+
1 row in set (0.053 sec)
MariaDB [vlrinst]> SELECT TIME_TO_SEC("01:00:00");
+-------------------------+
| TIME_TO_SEC("01:00:00") |
+-------------------------+
| 3600 |
+-------------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT TIME_TO_SEC("24:00:00");
+-------------------------+
| TIME_TO_SEC("24:00:00") |
+-------------------------+
| 86400 |
+-------------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT TIME_TO_SEC("-24:00:00");
+--------------------------+
| TIME_TO_SEC("-24:00:00") |
+--------------------------+
| -86400 |
+--------------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT TIMEDIFF("2021-06-06 13:15:11", "2021-06-06 13:10:10");
+--------------------------------------------------------+
| TIMEDIFF("2021-06-06 13:15:11", "2021-06-06 13:10:10") |
+--------------------------------------------------------+
| 00:05:01 |
+--------------------------------------------------------+
1 row in set (0.030 sec)
MariaDB [vlrinst]> SELECT TIMEDIFF("now(), "1982-06-06 13:10:10");
"> ;
"> Bye
'USE' is not recognized as an internal or external command,
operable program or batch file.
Mounika@MOUNIKA-PC c:\xampp
# use vlrinst;
'use' is not recognized as an internal or external command,
operable program or batch file.
Mounika@MOUNIKA-PC c:\xampp
# ram.cmd
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
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]> SELECT TIMEDIFF("now()", "1982-06-06 13:10:10");
+------------------------------------------+
| TIMEDIFF("now()", "1982-06-06 13:10:10") |
+------------------------------------------+
| NULL |
+------------------------------------------+
1 row in set, 1 warning (0.017 sec)
MariaDB [vlrinst]> SELECT TIMEDIFF("2021-06-06 13:15:11", "2000-06-06 13:10:10");
+--------------------------------------------------------+
| TIMEDIFF("2021-06-06 13:15:11", "2000-06-06 13:10:10") |
+--------------------------------------------------------+
| 838:59:59 |
+--------------------------------------------------------+
1 row in set, 1 warning (0.027 sec)
MariaDB [vlrinst]> SELECT TIMEDIFF("1982-06-06 13:15:11", "2000-06-06 13:10:10");
+--------------------------------------------------------+
| TIMEDIFF("1982-06-06 13:15:11", "2000-06-06 13:10:10") |
+--------------------------------------------------------+
| -838:59:59 |
+--------------------------------------------------------+
1 row in set, 1 warning (0.000 sec)
MariaDB [vlrinst]> SELECT TIMEDIFF("2021-06-06 13:15:11", "2000-06-06 13:10:10");
+--------------------------------------------------------+
| TIMEDIFF("2021-06-06 13:15:11", "2000-06-06 13:10:10") |
+--------------------------------------------------------+
| 838:59:59 |
+--------------------------------------------------------+
1 row in set, 1 warning (0.001 sec)
MariaDB [vlrinst]> SELECT TIMEDIFF("2021-06-06 13:15:11", "2021-06-06 13:10:10");
+--------------------------------------------------------+
| TIMEDIFF("2021-06-06 13:15:11", "2021-06-06 13:10:10") |
+--------------------------------------------------------+
| 00:05:01 |
+--------------------------------------------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT TIMEDIFF("2021-06-06 13:15:11", "2021-05-06 13:10:10");
+--------------------------------------------------------+
| TIMEDIFF("2021-06-06 13:15:11", "2021-05-06 13:10:10") |
+--------------------------------------------------------+
| 744:05:01 |
+--------------------------------------------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT TIMESTAMP("2017-07-23", "13:10:11");
+--------------------------------------+
| TIMESTAMP("2017-07-23", "13:10:11") |
+--------------------------------------+
| 2017-07-23 13:10:11 |
+--------------------------------------+
1 row in set (0.024 sec)
MariaDB [vlrinst]> SELECT TO_DAYS(now());
+----------------+
| TO_DAYS(now()) |
+----------------+
| 738314 |
+----------------+
1 row in set (0.010 sec)
MariaDB [vlrinst]> SELECT TO_DAYS(now());
+----------------+
| TO_DAYS(now()) |
+----------------+
| 738314 |
+----------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT TO_DAYS("0001-06-06 09:34:00");
+--------------------------------+
| TO_DAYS("0001-06-06 09:34:00") |
+--------------------------------+
| 522 |
+--------------------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT TO_DAYS("0001-00-00 09:34:00");
+--------------------------------+
| TO_DAYS("0001-00-00 09:34:00") |
+--------------------------------+
| NULL |
+--------------------------------+
1 row in set, 1 warning (0.000 sec)
MariaDB [vlrinst]> SELECT TO_DAYS("0001-00-01 09:34:00");
+--------------------------------+
| TO_DAYS("0001-00-01 09:34:00") |
+--------------------------------+
| NULL |
+--------------------------------+
1 row in set, 1 warning (0.000 sec)
MariaDB [vlrinst]> SELECT TO_DAYS("0001-01-01 09:34:00");
+--------------------------------+
| TO_DAYS("0001-01-01 09:34:00") |
+--------------------------------+
| 366 |
+--------------------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT week(now());
+-------------+
| week(now()) |
+-------------+
| 23 |
+-------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT weekday(now());
+----------------+
| weekday(now()) |
+----------------+
| 1 |
+----------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT weekofyear(now());
+-------------------+
| weekofyear(now()) |
+-------------------+
| 23 |
+-------------------+
1 row in set (0.007 sec)
MariaDB [vlrinst]> SELECT yearweek(now());
+-----------------+
| yearweek(now()) |
+-----------------+
| 202123 |
+-----------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT yearweek(now(),1);
+-------------------+
| yearweek(now(),1) |
+-------------------+
| 202123 |
+-------------------+
1 row in set (0.030 sec)
MariaDB [vlrinst]>
DATE functions sql videos in telugu 52
--------------------------------------------
The PERIOD_ADD() function adds a specified number of months to a period.
The PERIOD_ADD() function will return the result formatted as YYYYMM.
SELECT PERIOD_ADD(202106, 15);
------------
The PERIOD_DIFF() function returns the difference between two periods. The result will be in months.
Note: period1 and period2 should be in the same format.
SELECT PERIOD_DIFF(202103, 202403);
------------
The QUARTER() function returns the quarter of the year for a given date value (a number from 1 to 4).
January-March returns 1
April-June returns 2
July-Sep returns 3
Oct-Dec returns 4
SELECT QUARTER(CURDATE());
---------
The SECOND() function returns the seconds part of a time/datetime (from 0 to 59).
----------
he SEC_TO_TIME() function returns a time value (in format HH:MM:SS) based on the specified seconds.
SELECT SEC_TO_TIME(200);
-------------
The STR_TO_DATE() function returns a date based on a string and a format.
STR_TO_DATE(string, format)
string Required. The string to be formatted to a date
format Required. The format to use. Can be one or a combination of the following values:
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 (01 to 31)
%e Day of the month as a numeric value (0 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 (01 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 STR_TO_DATE("August,6,2021", "%M %e %Y");
SELECT STR_TO_DATE("Monday, May 14, 2017", "%W %M %e %Y");
SELECT STR_TO_DATE("2017,8,14 10,40,10", "%Y,%m,%d %h,%i,%s");
------------
The SUBDATE() function subtracts a time/date interval from a date and then returns the date.
SUBDATE(date, INTERVAL value unit)
SELECT SUBDATE("2021-06-15 09:34:21", INTERVAL 15 MINUTE);
-------
The SUBTIME() function subtracts time from a time/datetime expression and then returns the new time/datetime.
SUBTIME(datetime, time_interval)
Subtract 5 days3 hours, 2 minutes, 5.000001 seconds and return the datetime:
SELECT SUBTIME("2021-06-05 10:24:21.000004", "5 3:2:5.000001");
------------------------
Mounika@MOUNIKA-PC c:\xampp
# ram.cmd
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 13
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]> PERIOD_ADD()SELECT PERIOD_ADD(202106, 15);
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 'PERIOD_ADD()SELECT PERIOD_ADD(202
106, 15)' at line 1
MariaDB [vlrinst]> SELECT PERIOD_ADD(202106, 15);
+------------------------+
| PERIOD_ADD(202106, 15) |
+------------------------+
| 202209 |
+------------------------+
1 row in set (0.041 sec)
MariaDB [vlrinst]> SELECT PERIOD_ADD(205006, 100);
+-------------------------+
| PERIOD_ADD(205006, 100) |
+-------------------------+
| 205810 |
+-------------------------+
1 row in set (0.007 sec)
MariaDB [vlrinst]> SELECT PERIOD_DIFF(202103, 202403);
+-----------------------------+
| PERIOD_DIFF(202103, 202403) |
+-----------------------------+
| -36 |
+-----------------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT PERIOD_DIFF(202103, 200003);
+-----------------------------+
| PERIOD_DIFF(202103, 200003) |
+-----------------------------+
| 252 |
+-----------------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT PERIOD_DIFF(2103, 0003);
+-------------------------+
| PERIOD_DIFF(2103, 0003) |
+-------------------------+
| 252 |
+-------------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT QUARTER(CURDATE());
+--------------------+
| QUARTER(CURDATE()) |
+--------------------+
| 2 |
+--------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT second(CURDATE());
+-------------------+
| second(CURDATE()) |
+-------------------+
| 0 |
+-------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT second(CURDATE());
+-------------------+
| second(CURDATE()) |
+-------------------+
| 0 |
+-------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT second(now());
+---------------+
| second(now()) |
+---------------+
| 16 |
+---------------+
1 row in set (0.002 sec)
MariaDB [vlrinst]> select curdate();
+------------+
| curdate() |
+------------+
| 2021-06-05 |
+------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> select now();
+---------------------+
| now() |
+---------------------+
| 2021-06-05 23:23:46 |
+---------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT second(now()),now();
+---------------+---------------------+
| second(now()) | now() |
+---------------+---------------------+
| 0 | 2021-06-05 23:24:00 |
+---------------+---------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT second(now()),now();
+---------------+---------------------+
| second(now()) | now() |
+---------------+---------------------+
| 6 | 2021-06-05 23:24:06 |
+---------------+---------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT SEC_TO_TIME(200)
-> ;
+------------------+
| SEC_TO_TIME(200) |
+------------------+
| 00:03:20 |
+------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT SEC_TO_TIME(20000);
+--------------------+
| SEC_TO_TIME(20000) |
+--------------------+
| 05:33:20 |
+--------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT SEC_TO_TIME(200000);
+---------------------+
| SEC_TO_TIME(200000) |
+---------------------+
| 55:33:20 |
+---------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT STR_TO_DATE("August,6,2021", "%M %e %Y");
+------------------------------------------+
| STR_TO_DATE("August,6,2021", "%M %e %Y") |
+------------------------------------------+
| NULL |
+------------------------------------------+
1 row in set, 1 warning (0.040 sec)
MariaDB [vlrinst]> SELECT STR_TO_DATE("Monday, May 14, 2017", "%W %M %e %Y");
+----------------------------------------------------+
| STR_TO_DATE("Monday, May 14, 2017", "%W %M %e %Y") |
+----------------------------------------------------+
| NULL |
+----------------------------------------------------+
1 row in set, 1 warning (0.001 sec)
MariaDB [vlrinst]> SELECT STR_TO_DATE("2017,8,14 10,40,10", "%Y,%m,%d %h,%i,%s");
+--------------------------------------------------------+
| STR_TO_DATE("2017,8,14 10,40,10", "%Y,%m,%d %h,%i,%s") |
+--------------------------------------------------------+
| 2017-08-14 10:40:10 |
+--------------------------------------------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT STR_TO_DATE("August,6,2021", "%M, %e, %Y");
+--------------------------------------------+
| STR_TO_DATE("August,6,2021", "%M, %e, %Y") |
+--------------------------------------------+
| 2021-08-06 |
+--------------------------------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT STR_TO_DATE("Monday, May 14, 2017", "%W, %M, %e, %Y");
+-------------------------------------------------------+
| STR_TO_DATE("Monday, May 14, 2017", "%W, %M, %e, %Y") |
+-------------------------------------------------------+
| NULL |
+-------------------------------------------------------+
1 row in set, 1 warning (0.001 sec)
MariaDB [vlrinst]> SELECT STR_TO_DATE("Monday, May 14, 2017", "%W, %M %e, %Y");
+------------------------------------------------------+
| STR_TO_DATE("Monday, May 14, 2017", "%W, %M %e, %Y") |
+------------------------------------------------------+
| 2017-05-14 |
+------------------------------------------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT STR_TO_DATE("2017,8,14 10-4-10", "%Y,%m,%d %h,%i,%s");
+-------------------------------------------------------+
| STR_TO_DATE("2017,8,14 10-4-10", "%Y,%m,%d %h,%i,%s") |
+-------------------------------------------------------+
| NULL |
+-------------------------------------------------------+
1 row in set, 1 warning (0.001 sec)
MariaDB [vlrinst]> SELECT STR_TO_DATE("2017,8,14 10-4-10", "%Y,%m,%d %h-%i-%s");
+-------------------------------------------------------+
| STR_TO_DATE("2017,8,14 10-4-10", "%Y,%m,%d %h-%i-%s") |
+-------------------------------------------------------+
| 2017-08-14 10:04:10 |
+-------------------------------------------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT SUBTIME("2021-06-05 10:24:21.000004", "3:2:5.000001");
+-------------------------------------------------------+
| SUBTIME("2021-06-05 10:24:21.000004", "3:2:5.000001") |
+-------------------------------------------------------+
| 2021-06-05 07:22:16.000003 |
+-------------------------------------------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT SUBTIME("2021-06-05 10:24:21.000004", "5 3:2:5.000001");
+---------------------------------------------------------+
| SUBTIME("2021-06-05 10:24:21.000004", "5 3:2:5.000001") |
+---------------------------------------------------------+
| 2021-05-31 07:22:16.000003 |
+---------------------------------------------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]>
DATE FORMAT functions sql videos in telugu 51
------------------------------------------
MySQL DATE_SUB() Function
DATE_SUB(date, INTERVAL value interval)
Parameter Description
date Required. The date to be modified
value Required. The value of the time/date interval to subtract. Both positive and negative values are allowed
interval Required. The type of interval to subtract. Can be one of the following values:
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH
------------------
SELECT DATE_SUB("2021-06-15 11:31:55", INTERVAL 30 MINUTE);
SELECT DATE_SUB("2021-06-15", INTERVAL -6 MONTH);
SELECT DATE_SUB("2021-06-15 08:39:14", INTERVAL 5 HOUR);
9059868766
===========
The DAY() function returns the day of the month for a given date (a number from 1 to 31).
SELECT DAY(CURDATE());
==========
The DAYNAME() function returns the weekday name for a given date.
=========
The DAYOFMONTH() function returns the day of the month for a given date (a number from 1 to 31).
SELECT DAYOFMONTH("2021-06-15 09:34:21");
=======
The DAYOFWEEK() function returns the weekday index for a given date (a number from 1 to 7).
Note: 1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday, 7=Saturday.
SELECT DAYOFWEEK("2021-06-15 09:34:21");
===========
The DAYOFYEAR() function returns the day of the year for a given date (a number from 1 to 366).
SELECT DAYOFYEAR(CURDATE());
-----------
The EXTRACT() function extracts a part from a given date.
EXTRACT(part FROM date)
SELECT EXTRACT(WEEK FROM "2021-06-05");
SELECT EXTRACT(MINUTE FROM "2021-06-05 09:34:21");
SELECT EXTRACT(YEAR_MONTH FROM "2021-06-05 09:34:21");
==================
Definition and Usage
The FROM_DAYS() function returns a date from a numeric datevalue.
The FROM_DAYS() function is to be used only with dates within the Gregorian calendar.
Note: This function is the opposite of the TO_DAYS() function.
SELECT FROM_DAYS(780500);
==============
The HOUR() function returns the hour part for a given date (from 0 to 838).
SELECT HOUR("2017-06-20 09:34:00");
SELECT HOUR("838:59:59");
===========
The LAST_DAY() function extracts the last day of the month for a given date.
SELECT LAST_DAY("2017-02-10 09:34:00");
============
The LOCALTIME() function returns the current date and time.
Note: The date and time is returned as "YYYY-MM-DD HH-MM-SS" (string) or as YYYYMMDDHHMMSS.uuuuuu (numeric).
SELECT LOCALTIME() + 1;
===========
The LOCALTIMESTAMP() function returns the current date and time.
Note: The date and time is returned as "YYYY-MM-DD HH-MM-SS" (string) or as YYYYMMDDHHMMSS.uuuuuu (numeric).
SELECT LOCALTIMESTAMP() + 1;
-----------
SELECT MAKEDATE(2021, 3);
The MAKEDATE() function creates and returns a date based on a year and a number of days value.
SELECT MAKEDATE(2017, 366);
---------------
The MAKETIME() function creates and returns a time based on an hour, minute, and second value.
MAKETIME(hour, minute, second)
SELECT MAKETIME(21, 59, 59);
----------------
The MICROSECOND() function returns the microsecond part of a time/datetime (from 0 to 999999)
SELECT MICROSECOND("2017-06-20 09:34:00.000023");
-----------
The MINUTE() function returns the minute part of a time/datetime (from 0 to 59).
SELECT MINUTE("23:59:59");
-------------
The MONTH() function returns the month part for a given date (a number from 1 to 12).
SELECT MONTH("2021-06-05 09:34:21");
--------------
The MONTHNAME() function returns the name of the month for a given date.
SELECT MONTHNAME("2021-06-05");
--------------
The NOW() function returns the current date and time.
Note: The date and time is returned as "YYYY-MM-DD HH-MM-SS" (string) or as YYYYMMDDHHMMSS.uuuuuu (numeric).
-------------------
practice
MariaDB [vlrinst]> SELECT DATE_SUB("2021-06-15 11:31:55", INTERVAL 30 MINUTE);
+-----------------------------------------------------+
| DATE_SUB("2021-06-15 11:31:55", INTERVAL 30 MINUTE) |
+-----------------------------------------------------+
| 2021-06-15 11:01:55 |
+-----------------------------------------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT DATE_SUB("2021-06-15 11:31:55", INTERVAL 30 Hour);
+---------------------------------------------------+
| DATE_SUB("2021-06-15 11:31:55", INTERVAL 30 Hour) |
+---------------------------------------------------+
| 2021-06-14 05:31:55 |
+---------------------------------------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT DATE_SUB("2021-06-15 11:31:55", INTERVAL -30 Hour);
+----------------------------------------------------+
| DATE_SUB("2021-06-15 11:31:55", INTERVAL -30 Hour) |
+----------------------------------------------------+
| 2021-06-16 17:31:55 |
+----------------------------------------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT day("2021-06-15 11:31:55");
+----------------------------+
| day("2021-06-15 11:31:55") |
+----------------------------+
| 15 |
+----------------------------+
1 row in set (0.032 sec)
MariaDB [vlrinst]> SELECT day(cur_date());
ERROR 1305 (42000): FUNCTION vlrinst.cur_date does not exist
MariaDB [vlrinst]> SELECT day(curdate());
+----------------+
| day(curdate()) |
+----------------+
| 5 |
+----------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT dayname(curdate());
+--------------------+
| dayname(curdate()) |
+--------------------+
| Saturday |
+--------------------+
1 row in set (0.017 sec)
MariaDB [vlrinst]>
MariaDB [vlrinst]>
MariaDB [vlrinst]>
MariaDB [vlrinst]>
MariaDB [vlrinst]>
MariaDB [vlrinst]>
MariaDB [vlrinst]>
MariaDB [vlrinst]>
MariaDB [vlrinst]>
MariaDB [vlrinst]>
MariaDB [vlrinst]>
MariaDB [vlrinst]>
MariaDB [vlrinst]> SELECT DAYOFMONTH(curdate());
+-----------------------+
| DAYOFMONTH(curdate()) |
+-----------------------+
| 5 |
+-----------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT DAYOFWEEK(curdate());
+----------------------+
| DAYOFWEEK(curdate()) |
+----------------------+
| 7 |
+----------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT DAYOFYEAR(curdate());
+----------------------+
| DAYOFYEAR(curdate()) |
+----------------------+
| 156 |
+----------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT EXTRACT(YEAR_MONTH FROM "2021-06-05 09:34:21");
+------------------------------------------------+
| EXTRACT(YEAR_MONTH FROM "2021-06-05 09:34:21") |
+------------------------------------------------+
| 202106 |
+------------------------------------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT FROM_DAYS(780500);
+-------------------+
| FROM_DAYS(780500) |
+-------------------+
| 2136-12-08 |
+-------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT FROM_DAYS(1);
+--------------+
| FROM_DAYS(1) |
+--------------+
| 0000-00-00 |
+--------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT FROM_DAYS(2);
+--------------+
| FROM_DAYS(2) |
+--------------+
| 0000-00-00 |
+--------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT FROM_DAYS(1000);
+-----------------+
| FROM_DAYS(1000) |
+-----------------+
| 0002-09-27 |
+-----------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT FROM_DAYS(11000);
+------------------+
| FROM_DAYS(11000) |
+------------------+
| 0030-02-12 |
+------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT FROM_DAYS(111000);
+-------------------+
| FROM_DAYS(111000) |
+-------------------+
| 0303-11-29 |
+-------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT FROM_DAYS(1111000);
+--------------------+
| FROM_DAYS(1111000) |
+--------------------+
| 3041-10-25 |
+--------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT FROM_DAYS(211000);
+-------------------+
| FROM_DAYS(211000) |
+-------------------+
| 0577-09-12 |
+-------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT FROM_DAYS(311000);
+-------------------+
| FROM_DAYS(311000) |
+-------------------+
| 0851-06-28 |
+-------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT HOUR("838:59:59");
+-------------------+
| HOUR("838:59:59") |
+-------------------+
| 838 |
+-------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT HOUR(curdate());
+-----------------+
| HOUR(curdate()) |
+-----------------+
| 0 |
+-----------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT HOUR("2017-06-20 09:34:00");
+-----------------------------+
| HOUR("2017-06-20 09:34:00") |
+-----------------------------+
| 9 |
+-----------------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT HOUR("2017-06-20 09:34:00");
+-----------------------------+
| HOUR("2017-06-20 09:34:00") |
+-----------------------------+
| 9 |
+-----------------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> select curdate();
+------------+
| curdate() |
+------------+
| 2021-06-05 |
+------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> select now();
+---------------------+
| now() |
+---------------------+
| 2021-06-05 21:06:56 |
+---------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT HOUR(now());
+-------------+
| HOUR(now()) |
+-------------+
| 21 |
+-------------+
1 row in set (0.004 sec)
MariaDB [vlrinst]> SELECT LAST_DAY("2017-02-10 09:34:00");
+---------------------------------+
| LAST_DAY("2017-02-10 09:34:00") |
+---------------------------------+
| 2017-02-28 |
+---------------------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT LAST_DAY(now());
+-----------------+
| LAST_DAY(now()) |
+-----------------+
| 2021-06-30 |
+-----------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT LAST_DAY("2016-02-10 09:34:00");
+---------------------------------+
| LAST_DAY("2016-02-10 09:34:00") |
+---------------------------------+
| 2016-02-29 |
+---------------------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> select localtime();
+---------------------+
| localtime() |
+---------------------+
| 2021-06-05 21:16:35 |
+---------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> select now();
+---------------------+
| now() |
+---------------------+
| 2021-06-05 21:16:44 |
+---------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> select LOCALTIMESTAMP();
+---------------------+
| LOCALTIMESTAMP() |
+---------------------+
| 2021-06-05 21:17:18 |
+---------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT MAKEDATE(2021, 3);
+-------------------+
| MAKEDATE(2021, 3) |
+-------------------+
| 2021-01-03 |
+-------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT MAKEDATE(2021, 300);
+---------------------+
| MAKEDATE(2021, 300) |
+---------------------+
| 2021-10-27 |
+---------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT MAKEDATE(2000, 60);
+--------------------+
| MAKEDATE(2000, 60) |
+--------------------+
| 2000-02-29 |
+--------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT MAKEDATE(20001, 60);
+---------------------+
| MAKEDATE(20001, 60) |
+---------------------+
| NULL |
+---------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT MAKEDATE(2000, 60);
+--------------------+
| MAKEDATE(2000, 60) |
+--------------------+
| 2000-02-29 |
+--------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]>
Mounika@MOUNIKA-PC c:\xampp
# ram.cmd
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
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]> SELECT MAKETIME(21, 59, 59);
+----------------------+
| MAKETIME(21, 59, 59) |
+----------------------+
| 21:59:59 |
+----------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT MAKETIME(25, 59, 59);
+----------------------+
| MAKETIME(25, 59, 59) |
+----------------------+
| 25:59:59 |
+----------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT MAKETIME(25, 59, 591);
+-----------------------+
| MAKETIME(25, 59, 591) |
+-----------------------+
| NULL |
+-----------------------+
1 row in set, 1 warning (0.000 sec)
MariaDB [vlrinst]> select MICROSECOND(now());
+--------------------+
| MICROSECOND(now()) |
+--------------------+
| 0 |
+--------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT MICROSECOND("2017-06-20 09:34:00.000023");
+-------------------------------------------+
| MICROSECOND("2017-06-20 09:34:00.000023") |
+-------------------------------------------+
| 23 |
+-------------------------------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT minite("2017-06-20 09:34:00.000023");
ERROR 1305 (42000): FUNCTION vlrinst.minite does not exist
MariaDB [vlrinst]> SELECT minute("2017-06-20 09:34:00.000023");
+--------------------------------------+
| minute("2017-06-20 09:34:00.000023") |
+--------------------------------------+
| 34 |
+--------------------------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT minute(now());
+---------------+
| minute(now()) |
+---------------+
| 23 |
+---------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT month(now());
+--------------+
| month(now()) |
+--------------+
| 6 |
+--------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT monthname(now());
+------------------+
| monthname(now()) |
+------------------+
| June |
+------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> now()
-> ;
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 'now()' at line 1
MariaDB [vlrinst]> select now();
+---------------------+
| now() |
+---------------------+
| 2021-06-05 21:24:18 |
+---------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]>
CURDATE,CURTIME,date,DATEDIFF date functions sql videos in telugu 50
---------------------------------------------
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]>
Subscribe to:
Posts (Atom)