hi guys saloni this side and welcome back to our Channel code era so this is the 27th video of our SQL tutorial series and in the last video we discussed about the numeric functions and if you want to watch that video then please check out the video link in the description box so let's start our discussion so guys as we already know we have these five type of built-in functions so out of this five we have already discussed the aggregate functions string functions and numeric functions so in today's video we will discuss about the date and time functions in detail and with this video I will also share the SQL practice questions so please watch this video till the end so that you can solve those question and guys if you are new to our Channel then please subscribe our channel to get the latest updates so now let's continue our discussion so guys in date and time function first we will discuss these three functions which are current date current time and current timestamp so the definition of these functions are clear by their name so the current date Returns the current date the current time Returns the current time and the current time stamp is used to get the current timestamp so let's execute them one by one so first we will face the current date so we will write select current underscore date so let's execute this query so guys you can see in the output this is our current date now let's find out the current time so I will write current time so let's see so this is the current time of this system now let's find out the current timestamp so the timestamp is the combination of date and time so I will write time stamp so let's execute this query so guys you can see this is the current timestamp which includes date and time so guys as we know the timestamps include date and time now suppose we have a timestamp as input and we want to display only date or only time in the output so for such cases we have these two functions which are date and time so the date function returns only date from a date time expression or time stamp while the time function returns only time from the date time expression or timestamp so let's go to the MySQL workbench to see how this function works so now let's first select date from a timestamp so we will write select date and parenthesis we have to pass the timestamp so I will pass this timestamp so let's execute this query so guys you can see this function returns only date from a timestamp and if you want to get only time then you have to write here time function so let's see so this is very easy to use date and time function and in parentheses you can also pass our previous function which is current timestamp so I will write current underscore timestamp so it will return the current timestamp so let's execute this query so you can see in the output we have only time so in this way you can also pass a function inside another function so after the date and time function let's discuss few more functions so guys let's suppose you have a date or date time expression as input but you want to display only Year from a date or you want to display only month or day so for this purpose we have three different functions so the first function is year which is used to extract Year from date or the date time expression and after this our next function is month so this function returns only month and the third function is day which returns only day from a date or a date time expression so let's use this function one by one so first we will face ER from a date time expression so I will write select year and in parenthesis I will pass the date time expression so here is our expression so let's execute this query so guys you can see in the output we have only here from this date time expression likewise you can fetch only month so I will write month and this function will return the month from the date time expression and in the same way you can use the day function so this function will only return the day so let's execute this query so you can see in the output we have only day so in this way you can use these three functions which are very easy to use so likewise date if we have time or date time expression as input then we can also extract only hours in the output or we can also get minute or seconds in output so for this we have to use these three functions so we have to use our function to extract our from a time or date time expression likewise minute and second function to extract only minute or second from a time or date time expression respectively so let's see one example using this function so guys here in this query we are using minute function on a date time expression so let's execute this query so guys you can see our output is 30 which is the minute in the given date time expression likewise you can use our or second function as per your requirement so guys we recently discussed these six functions but here we have an alternate function which you can use instead of all these functions so the name of the function is extract so guys the extract function returns a specific component from a date time or a date time expression like you can extract Year Day hours and so on so here is the syntax to use the extract function so guys with this function we have to pass a component as a parameter so here the component is the part of date time expression that you want to extract and then we have to write the keyword from after that we have to pass our date time expression as parameter so guys with the help of this function you can extract any of this component so in syntax in place of component you have to write this keyword as per your need so let's solve a question for better understanding so our question is written ID name and only the day from the date of birth of the employee s birthday from the employee table so to solve this question we will write select ID comma name and then we will use the extract function so in parenthesis first we will write day keyword which is a component so here we are writing day keyword because we want to display only day in the output after that we will write from keyword and then at the place of date time expression we will pass our column name which is date of birth so let's execute this query and see the output so guys you can see in the output in the birthday column we have only day from the date of birth column of the employee table so guys after the extract our next function is date difference so this function Returns the difference between two dates or timestamp so to use this function we have to pass two dates or date time expression so for example this query so here with the date difference function we are passing two dates and guys always remember one thing the date 1 should be more than date two otherwise you will get negative results so let's execute this query and see the output so our output is 4 which is the correct output so using this function you can get the difference between two dates or date time expiration likewise we have time difference function so this function Returns the difference between two times or timestamps so here is the syntax to use time difference function so here we have to pass time 1 and time 2 as parameter or you can also pass date time expression or timestamp so here is one example with date time expression as parameter and guys here also the time 1 must be greater than time 2 otherwise this function will also written the negative output so let's execute this query and see the output so guys our output is 15 minute which is the correct output because in both the parameters date is same and the difference between 10 30 and 10 15 is 15 minutes so now let's change the date of the first parameter from 1st June to Second June so let's execute this query and see the output so guys our output is 24 hour and 15 minutes so guys this function Returns the output in the time format that's why we are getting the output as 24 hour and 15 minute so it's very easy to use date difference and time difference function so after this our next function is date 8. so guys with the help of this function we can add a time or date interval to a date or timestamp and then this function will return the calculated date or date time expression as result so here is the syntax to use this function so with this function we have to pass date or date time expression to which we want to add the date or time interval and after a comma we have to write interval keyword after this we have to provide a numeric value for the interval and in the last we have to pass interval unit so as interval unit you can pass any of this available option so let's solve one question for better understanding so our question is write a SQL query to display ID name and the date of birth from the employee table also add one month to the date of birth of the employee as column name updated date of birth so guys as solution this will be our query so here our interval value is 1 and the interval unit is month because as per our question we have to add one month in the date of birth of each employee so let's execute this query and see the results so guys you can see in the output for each employee one month is added to their date of birth so this is how you can use date 8 function and guys you can also subtract a time or date interval from a date or timestamp using date sub function so instead of writing date 8 function you just have to write date sub and the rest of the query will be same as before so here we have a question based on this function so the question is write a SQL query to display ID name and date of birth from the employee table also subtract three years from the date of birth of the employees as column name updated date of birth so try to solve this query at your end and tell me the correct query in the comment section so after this our last date and time function is date format which is very useful and important so with the help of this function we can display data of date or date time expression in different formats like we can display this date into this format or into this format or this date time expression into this formats and so on so to change the format we have to pass to parameter to this function so the first parameter is date or date time itself and the second parameter is the format so guys in SQL there are many formats are available something like this and this so we can use this format as per our need so guys you can pause this screen to check out the formats so let's solve a question for better understanding so our question is display ID name and the date of birth in this given format as new date of birth from the employee table so here we have to display a date of birth in this format so our function will be like this so as format first we will write percent capital D which will display the day of the month and then we will write percent small B which will display the name of the month and then we will write percent capital Y which will display the year in the 4 digits and guys always remember you have to write the format inside single quotes so our final query will be like this so here in place of date we have to pass the column name which is date of birth and here we also have to fetch ID and name from the employee table so let's execute this query so guys this is what we want to get in the output so I hope now you understand how to use date format function and here I have one more question so that you don't have any doubt regarding this function so the question is display the given timestamp into this format so guys we want to display only time from this timestamp in this format so our query will be like this and this will be our format so let's execute this query so guys this is our output but this is not the correct output because we want to display the time in this format so here colon is missing so for this we have to pass manually the colon in the format something like this so let's execute this query so now our output is correct so guys in this way you can use date format function so guys that's all about date and time function and guys it is possible that few function will not work in any other dbms and it is also not possible to cover all those functions in one video so if you are using any other dbms and you are facing any issue with any function then please ask your query in the comment section I will definitely provide you the alternate function according to your dbms and if you want to practice more on date and time function then please check out the SQL practice PDF Link in the description box and guys in the next video we will discuss about the conversion functions so till then keep watching Code era and don't forget to like this video And subscribe our Channel thank you so much for watching