Date Functions in SQL | Interview Questions on DATE Functions | DATEPART, DATEADD,DATEDIFF Functions

In this video we will talk about date functions in sql. Date functions are very powerful and easy to use. We will talk about 3 most important functions:
datepart
datediff
dateadd

[ritikajaiswal]

The way you solve interview questions is just mind blowing. I think you should keep making more videos on interview questions. It's a gem

[ls]

Appreciated your efforts. I really liked the way of explanation.

Tried to solve the question asked:

select current_date(),
dateadd(days, 6, current_date()) AS POST_DATE /*Date After adding 6 days*/
, datediff(week,current_date(),POST_DATE) AS WEEK /* Week differnece between postdate and current_Date */
, dateadd(days,2*week,POST_DATE) AS POST_DATE_WITH_WEEKDAYS_ONLY /* Add additional days based on no weeks difference */
;

[ashwanibhati]

Create both tables shown in video by using these two codes

/*table 1*/

DROP TABLE IF EXISTS customer_orders;
create table customer_orders (
order_id integer,
customer_id integer,
order_date date,
ship_date date);

insert into customer_orders values(1000, 1, cast('2022-01-05' as date), cast('2022-01-11' as date))
, (1001,2,cast('2022-02-04' as date), cast('2022-02-16' as date))
, (1002,3,cast('2022-01-01' as date), cast('2022-01-19' as date))
, (1003,4,cast('2022-01-06' as date), cast('2022-01-30' as date))
, (1004,1,cast('2022-02-07' as date), cast('2022-02-13' as date))
, (1005,4,cast('2022-01-07' as date), cast('2022-01-31' as date))
, (1006,3,cast('2022-02-08' as date), cast('2022-02-26' as date))
, (1007,2,cast('2022-02-09' as date), cast('2022-02-21' as date))
, (1008,4,cast('2022-02-10' as date), cast('2022-03-06' as date))
;

SELECT * FROM customer_orders;

/*table 2*/

DROP TABLE IF EXISTS customer;
create table customer (
customer_id integer,
customer_name VARCHAR(10),
gender VARCHAR(1),
dob date);

insert into customer values
(1, 'Rahul', 'M', cast('2000-01-05' as date))
, (2,'Shilpa','F',cast('2004-04-05' as date))
, (3,'Ramesh','M',cast('2003-07-07' as date))
, (4,'Katrina','F',cast('2005-02-05' as date))
, (5,'Alia','F',cast('1992-01-01' as date))
;

SELECT * FROM customer;

[mohit]

thank you very much, sir.. please create a separate playlist that consists of all your videos that are not in your previous you in advance, really helpful.

[My-Research]

Randomly found your channel super explanation.
You are The King 👑 in SQL.

[idhwanibhatt]

Found your channel today on LinkedIn and checked out. Just wanna say what an amazing initiative you have taken Ankit to get us introduced with Industry Interview questions. Want more such Interview Question videos along with Leetcode problems.

[gauravsharma]

Bro you are a GEM. thank you for making these videos.
i solved some of the trickiest questions only after watching your videos.🤘🤘

[nishabansal]

Very useful videos with scenario, keep it up

[adityeshchaturvedi]

Loved every piece of it.
Crystal clear explanation!

[idwtv]

sir if a product is ordered on 3rd jan 2022 but got shipped on 13th jan 2022 i.e. 10 days it will be considered 2 weeks and we will subtract 2 X 2 = 4 as weekends by there is only one sat sun between 3rd jan 2022 to 13th jan 2022

[keifer]

9:30 For the first row, how are we sure that there were 2 weekend days in the 6 days it took to ship? It could've been Sunday to Friday, therefore only one weekend day. Please clarify, thanks

[viveknegi]

If the number of days to ship is 6 days, then this query will give 6-(2*1)=4 days as working days. But it can be possible that there is only one weekend in these 6 days then correct answer will be 6-1=5 days. So, I feel there should another way to reduce weekends.

[readname]

HI Ankit, Can you please let us know if it is in weekend how to proceed further?

[AbhishekKumar-bhis]

You are doing great bro please post everyday one question answer

[sabyasachiadhikary]

Thank you very much Ankit sir for the video, Really helpful.

[tupaiadhikari]

Inspired From Karan Gupta's Post
```
SELECT
*,
CASE WHEN WEEKDAY(ship_date)=5 THEN DATE_ADD(ship_date, INTERVAL 2 DAY)
WHEN WEEKDAY(ship_date)=6 THEN DATE_ADD(ship_date, INTERVAL 1 DAY)
ELSE ship_date END AS newShipDate
FROM
customer_order;

[gajanantiwari]

Hi @ankitbansal, these functions dont work in Oracle. It is only valid in mysql. But still Thanks for clearing the doubts. Really helpful

[TheVaibhavdang]

Solution for adding the days in respect to customerorders table

with cte as (
Select orderid as "orderid", customerid as "customerid", order_date as "order_date", ship_date as "ship_date", DATEDIFF(day, order_date, ship_date) as days_to_ship,
DATEDIFF(week, order_date, ship_date) as week_between,
DATEDIFF(day, order_date, ship_date) - 2*DATEDIFF(week, order_date, ship_date) as business_days
from customerorders)

Select DATEADD(day, cast(c.business_days as int), c.ship_date) as
from cte c

[yatinshekhar]

7/141
Hi sir,
How to count age in MYSQL coz
In SQL Server, datediff( ) takes three parameters
but in MYSQL it only takes two parameter that are date, so when I am using datediff( ) it is giving me no. of days
and then I have to divide it with 365, to get the date
Is their any other alternative?
Thanks.

[sahilummat]

Hello Sir, Can you please make a video as to how to approch the problem . Like how to break it in parts and then solve . Some guidance on this would be really helpful