por There are many date and time functions in SQL that can help you control this very important element within databases and the programs you develop from one. So, this time we will tell you about current, date and date_format, 3 date and time functions in SQL.
Date and time functions in SQL
Current, date and date_format are three date and time functions in SQL that allow you to get data in the form of a date. These will allow you to configure the way you want to capture important dates within your data program.
Get datetime with current
First of all, to get the date and time with the statement current you have to know that, in this way, you will get the current date on which the date or data set is being entered. To do this, there are two ways: CURDATE or CURTIME, as well as CURRENT_DATE and CURRENT_time. Both pairs allow you to get the current date and current time.
To execute it, you will have to position yourself in the database and then establish each of the statements with the SELECT element, which we establish below:
USE SPRINGFIELD
SELECT CURDATE() DATE;
SELECT CURTIME() DATE;
SELECT CURRENT_DATE() ;
SELECT CURRENT_TIME();
SELECT CURRENT_TIMESTAMP(), LOCALTIME(), LOCALTIMESTAMP(), NOW(), SYSDATE();
Get the date and time with date
On the other hand, to obtain the dates within a database in SQL, you can use the command date. To do this, you will only have to establish the date in parentheses, following a format established according to your requirements, and execute the statement.
The use of this statement can be reflected as follows:
USE SPRINGFIELD
SELECT DATE (‘2022 – 11 – 10’);
SELECT DATE (‘2022 – 11 – 10 20:30:15’);
SELECT DATE (‘2022 – 11 – 10 20:30:15.00000001’);
SELECT DATE (‘The date of the first broadcast was 2022 – 11 – 10’);
SELECT DATE(NULL);
Get date and time with date_format
Finally, if you want to get the date and time with the command date_formatyou have to know the various ways to do it, since apart from the date you give to the command, you will have to decide what you want to obtain and in what format you will receive it.
In addition to the date inside the parentheses, you will need to set the format, which can be uppercase (Y, M, D) and lowercase (y, m, d). When you use the first option, you will get the value in text, while when you use the second you will get the value in number:
SELECT DATE_FORMAT (‘2022 – 11 – 10 20:30:15.00000001’, ‘%Y’);
SELECT DATE_FORMAT (‘2022 – 11 – 10 20:30:15.00000001’, ‘%y’);
SELECT DATE_FORMAT (‘2022 – 11 – 10 20:30:15.00000001’, ‘%M’);
SELECT DATE_FORMAT (‘2022 – 11 – 10 20:30:15.00000001’, ‘%m’);
SELECT DATE_FORMAT (‘2022 – 11 – 10 20:30:15.00000001’, ‘%D’);
SELECT DATE_FORMAT (‘2022 – 11 – 10 20:30:15.00000001’, ‘%d’);
SELECT DATE_FORMAT (‘2022 – 11 – 10 20:30:15.00000001’, ‘%d, %m, %Y’);
SELECT DATE_FORMAT (‘2022 – 11 – 10 20:30:15.00000001’, ‘%D, %M, %Y’);
What is left to learn?
The simple answer to this question is short: there is a lot to learn, since these 3 date and time functions in SQL are only a small group of the many possibilities that exist to obtain or check dates in a database. That is why, for you to continue in this process, we want to invite you to our Big Data, Artificial Intelligence & Machine Learning Full Stack Bootcamp. Sign up and change your life in a few months!