Translate

Friday, 8 October 2021

ADDDATE ADDTIME Function date functions sql videos in telugu 49

 https://youtu.be/37FngpBOT28

-------------------------------------------
MySQL CURDATE() Function

The CURDATE() function returns the current date.

The date is returned as "YYYY-MM-DD" (string) or as YYYYMMDD (numeric).

Note: This function equals the CURRENT_DATE() function.

SELECT CURDATE() + 1;

----------
MySQL CURRENT_DATE() Function
--------
MySQL CURRENT_TIME() Function
MySQL CURTIME() Function
-----------
MySQL CURRENT_TIMESTAMP() Function
------------
The DATE() function extracts the date part from a datetime expression.

SELECT DATE("2021-06-03 09:34:21");
----------
The DATEDIFF() function returns the number of days between two date values.
DATEDIFF(date1, date2)

SELECT DATEDIFF("2021-06-25 09:34:21", "2017-05-02 15:25:35");
------------
The DATE_ADD() function adds a time/date interval to a date and then returns the date.

DATE_ADD(date, INTERVAL value addunit)
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_ADD("2017-06-15", INTERVAL -2 MONTH);
=======================


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]> curdate();
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 'curdate()' at line 1
MariaDB [vlrinst]> select curdate();
+------------+
| curdate()  |
+------------+
| 2021-06-03 |
+------------+
1 row in set (0.001 sec)

MariaDB [vlrinst]> select curdate()+0;
+-------------+
| curdate()+0 |
+-------------+
|    20210603 |
+-------------+
1 row in set (0.000 sec)

MariaDB [vlrinst]> select curdate()+3;
+-------------+
| curdate()+3 |
+-------------+
|    20210606 |
+-------------+
1 row in set (0.001 sec)

MariaDB [vlrinst]> select currentdate()+3;
ERROR 1305 (42000): FUNCTION vlrinst.currentdate does not exist
MariaDB [vlrinst]> select current-date()+3;
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 ')+3' at line 1
MariaDB [vlrinst]> select current_date()+3;
+------------------+
| current_date()+3 |
+------------------+
|         20210606 |
+------------------+
1 row in set (0.000 sec)

MariaDB [vlrinst]> select current_date();
+----------------+
| current_date() |
+----------------+
| 2021-06-03     |
+----------------+
1 row in set (0.000 sec)

MariaDB [vlrinst]> select current_time();
+----------------+
| current_time() |
+----------------+
| 23:26:00       |
+----------------+
1 row in set (0.000 sec)

MariaDB [vlrinst]> select current_time();
+----------------+
| current_time() |
+----------------+
| 23:26:06       |
+----------------+
1 row in set (0.000 sec)

MariaDB [vlrinst]> select current_time()+20;
+-------------------+
| current_time()+20 |
+-------------------+
|            232633 |
+-------------------+
1 row in set (0.000 sec)

MariaDB [vlrinst]> select current_time()+20,current_time();
+-------------------+----------------+
| current_time()+20 | current_time() |
+-------------------+----------------+
|            232654 | 23:26:34       |
+-------------------+----------------+
1 row in set (0.000 sec)

MariaDB [vlrinst]> select curtime()+20,current_time();
+--------------+----------------+
| curtime()+20 | current_time() |
+--------------+----------------+
|       232668 | 23:26:48       |
+--------------+----------------+
1 row in set (0.000 sec)

MariaDB [vlrinst]> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2021-06-03 23:27:15 |
+---------------------+
1 row in set (0.000 sec)

MariaDB [vlrinst]> select current_timestamp()+15;
+------------------------+
| current_timestamp()+15 |
+------------------------+
|         20210603232740 |
+------------------------+
1 row in set (0.000 sec)

MariaDB [vlrinst]> select date(current_timestamp());
+---------------------------+
| date(current_timestamp()) |
+---------------------------+
| 2021-06-03                |
+---------------------------+
1 row in set (0.019 sec)

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.001 sec)

MariaDB [vlrinst]> select date(created_at),Created_at from vlr;
+------------------+---------------------+
| date(created_at) | Created_at          |
+------------------+---------------------+
| 2021-05-26       | 2021-05-26 22:32:47 |
| 2021-05-26       | 2021-05-26 22:36:01 |
+------------------+---------------------+
2 rows in set (0.001 sec)

MariaDB [vlrinst]> SELECT DATE("2021-06-03 09:34:21");
+-----------------------------+
| DATE("2021-06-03 09:34:21") |
+-----------------------------+
| 2021-06-03                  |
+-----------------------------+
1 row in set (0.000 sec)

MariaDB [vlrinst]> select Datediff("2017-05-02",curDate());
+----------------------------------+
| Datediff("2017-05-02",curDate()) |
+----------------------------------+
|                            -1493 |
+----------------------------------+
1 row in set (0.001 sec)

MariaDB [vlrinst]> select Datediff("2017-05-02",curDate());
+----------------------------------+
| Datediff("2017-05-02",curDate()) |
+----------------------------------+
|                            -1493 |
+----------------------------------+
1 row in set (0.000 sec)

MariaDB [vlrinst]> select Datediff("2017-05-02","1947-08-15");
+-------------------------------------+
| Datediff("2017-05-02","1947-08-15") |
+-------------------------------------+
|                               25463 |
+-------------------------------------+
1 row in set (0.001 sec)

MariaDB [vlrinst]>

Timestamp with update example sql videos in telugu 48

 https://youtu.be/bQX_NNDz6QM

--------------------------------------------
MySQL ADDDATE () Function

ADDDATE(date, INTERVAL value addunit)
ADDDATE(date, days)

The ADDDATE() function adds a time/date interval to a date and then returns the date.

date Required.  The date to be modified

days Required. The number of days to add to date

value Required. The value of the time/date interval to add. Both positive and negative values are allowed

addunit Required. The type of interval to add. 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 ADDDATE("2021-06-03", INTERVAL 10 DAY);
SELECT ADDDATE("2021-06-03 09:34:21", INTERVAL 15 MINUTE);



-------------------
MySQL ADDTIME() Function

ADDTIME(datetime, addtime)

SELECT ADDTIME("2017-06-15 09:34:21", "2");


Example
Add 5 seconds and 3 microseconds to a time and return the datetime:

SELECT ADDTIME("2017-06-15 09:34:21.000001", "5.000003");


Example
Add 2 hours, 10 minutes, 5 seconds, and 3 microseconds to a time and return the datetime:

SELECT ADDTIME("2017-06-15 09:34:21.000001", "2:10:5.000003");

Example
Add 5 days, 2 hours, 10 minutes, 5 seconds, and 3 microseconds to a time and return the datetime:

SELECT ADDTIME("2017-06-15 09:34:21.000001", "5 2:10:5.000003");


Example
Add 2 hours, 10 minutes, 5 seconds, and 3 microseconds to a time and return the time:

SELECT ADDTIME("09:34:21.000001", "2:10:5.000003");




================================

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]> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| classicmodels      |
| information_schema |
| mysql              |
| performance_schema |
| phpmyadmin         |
| test               |
| vlrinst            |
+--------------------+
7 rows in set (0.108 sec)

MariaDB [vlrinst]> show tables;
+-------------------+
| Tables_in_vlrinst |
+-------------------+
| dt                |
| emp               |
| emp1              |
| people            |
| ts                |
| vlr               |
+-------------------+
6 rows in set (0.001 sec)

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.160 sec)

MariaDB [vlrinst]> select adddate("2021-06-03", interval 10 day);
+----------------------------------------+
| adddate("2021-06-03", interval 10 day) |
+----------------------------------------+
| 2021-06-13                             |
+----------------------------------------+
1 row in set (0.096 sec)

MariaDB [vlrinst]> select created_at,adddate(created_at,interval 20 day) from vlr;
+---------------------+-------------------------------------+
| created_at          | adddate(created_at,interval 20 day) |
+---------------------+-------------------------------------+
| 2021-05-26 22:32:47 | 2021-06-15 22:32:47                 |
| 2021-05-26 22:36:01 | 2021-06-15 22:36:01                 |
+---------------------+-------------------------------------+
2 rows in set (0.029 sec)

MariaDB [vlrinst]> select created_at,adddate(created_at,30) from vlr;
+---------------------+------------------------+
| created_at          | adddate(created_at,30) |
+---------------------+------------------------+
| 2021-05-26 22:32:47 | 2021-06-25 22:32:47    |
| 2021-05-26 22:36:01 | 2021-06-25 22:36:01    |
+---------------------+------------------------+
2 rows in set (0.001 sec)

MariaDB [vlrinst]> select created_at,adddate(created_at,-30) from vlr;
+---------------------+-------------------------+
| created_at          | adddate(created_at,-30) |
+---------------------+-------------------------+
| 2021-05-26 22:32:47 | 2021-04-26 22:32:47     |
| 2021-05-26 22:36:01 | 2021-04-26 22:36:01     |
+---------------------+-------------------------+
2 rows in set (0.001 sec)

MariaDB [vlrinst]> select created_at,adddate(created_at,interval 20 hour) from vlr;
+---------------------+--------------------------------------+
| created_at          | adddate(created_at,interval 20 hour) |
+---------------------+--------------------------------------+
| 2021-05-26 22:32:47 | 2021-05-27 18:32:47                  |
| 2021-05-26 22:36:01 | 2021-05-27 18:36:01                  |
+---------------------+--------------------------------------+
2 rows in set (0.002 sec)

MariaDB [vlrinst]> select created_at,adddate(created_at,interval 2 year) from vlr;
+---------------------+-------------------------------------+
| created_at          | adddate(created_at,interval 2 year) |
+---------------------+-------------------------------------+
| 2021-05-26 22:32:47 | 2023-05-26 22:32:47                 |
| 2021-05-26 22:36:01 | 2023-05-26 22:36:01                 |
+---------------------+-------------------------------------+
2 rows in set (0.001 sec)

MariaDB [vlrinst]> SELECT ADDTIME("2021-06-03 09:34:21", "2");
+-------------------------------------+
| ADDTIME("2021-06-03 09:34:21", "2") |
+-------------------------------------+
| 2021-06-03 09:34:23                 |
+-------------------------------------+
1 row in set (0.018 sec)

MariaDB [vlrinst]> select created_at,addtime(created_at,2:10:30) 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 ':10:30) from vlr' at line 1
MariaDB [vlrinst]> select created_at,addtime(created_at,"2:10:30") from vlr;
+---------------------+-------------------------------+
| created_at          | addtime(created_at,"2:10:30") |
+---------------------+-------------------------------+
| 2021-05-26 22:32:47 | 2021-05-27 00:43:17           |
| 2021-05-26 22:36:01 | 2021-05-27 00:46:31           |
+---------------------+-------------------------------+
2 rows in set (0.001 sec)

MariaDB [vlrinst]> select created_at,addtime(created_at,"2:10:30.0000020") from vlr;
+---------------------+---------------------------------------+
| created_at          | addtime(created_at,"2:10:30.0000020") |
+---------------------+---------------------------------------+
| 2021-05-26 22:32:47 | 2021-05-27 00:43:17.000002            |
| 2021-05-26 22:36:01 | 2021-05-27 00:46:31.000002            |
+---------------------+---------------------------------------+
2 rows in set, 2 warnings (0.015 sec)

MariaDB [vlrinst]> select created_at,addtime(created_at,"3 1:10:30.0000025") from vlr;
+---------------------+-----------------------------------------+
| created_at          | addtime(created_at,"3 1:10:30.0000025") |
+---------------------+-----------------------------------------+
| 2021-05-26 22:32:47 | 2021-05-29 23:43:17.000002              |
| 2021-05-26 22:36:01 | 2021-05-29 23:46:31.000002              |
+---------------------+-----------------------------------------+
2 rows in set, 2 warnings (0.000 sec)

MariaDB [vlrinst]>