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

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;

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.

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

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 */ ;

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

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.

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;

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 order_date_addition_to_businessdays from cte c

Very useful videos with scenario, keep it up

Concern on the logic to get business days Case : If order was placed on monday and delivered on Wednesday then by your logic business days would be 0 Because different between dates : 2 Difference in weeks : 1

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

Loved every piece of it. Crystal clear explanation!

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

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

You are doing great bro please post everyday one question answer

Sir, just a small Question..... Do these functions work only if the dates are in YYYY-MM-DD format???

Assuming the business days to add is stored in a column named business_days Select case when datediff(week,order_date,dateadd(day,business_days,order_date) ) > 0 Then dateadd(day,business_days+2*datediff(week,order_date,dateadd(day,business_days,order_date) ) ,order_date) else dateadd(day,business_days,order_date) End as ship_date

second way : select case when datepart(weekday,getdate()) in (1,7) then dateadd(day,6,getdate()) else dateadd(day,7,getdate()) end

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

Helpful.. great explanation

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

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

with datedifft as(select DATEadd(day,7,'2022-01-23')as dateq) ,diff as(select DATEDIFF(week ,'2022-01-23',(select dateq from datedifft))as dater) select (select dateq from datedifft)+2*(select dater from diff)

but that is not a correct approach to find business days, lets say the we have 12 days gap, and it is starting from Saturday, then in 12 days of gap there will be 2 Saturdays and 2 sundays, that way we need to subs 4.

Dateadd( day, datediff(week,order_date,dateadd(day, business_day,ordered)*2+datediff(day,order_date,dateadd(day, business_day,order_date), order_date ) as ship_date From customers

what if the difference between order date and ship date are 3days? Then the business days calculation will fail

select case when date_part(dayofweek,sysdate-4) >=0 and date_part(dayofweek,sysdate-4)

with cte as ( SELECT *, datename(DW,ship_date) as nameofthe_day FROM customer_orderss ) select order_id,customer_id,order_date,ship_date,nameofthe_day, case when nameofthe_day= 'Sunday' then datename(dw,dateadd(day,1,ship_date)) when nameofthe_day= 'Saturday' then datename(dw,dateadd(day,2,ship_date)) else nameofthe_day end as busi_day from cte

Hi Ankit thanks for this video may I know in which video you provided the solution

--Assignment Question select case when DATEPART(weekday,'2022-12-26')IN(7) then DATEADD(day,10,'2022-12-26') when DATEPART(weekday,'2022-12-26')IN(5,6) then DATEADD(day,11,'2022-12-26') ELSE DATEADD(day,9,'2022-12-26') END as date_add_7

How about this query where we consider order date can be a weekend and we use a temp_date and update orderdate to temp date when saturday 2 days added and when sunday 1 day is added now we follow the same thing; WITH cte AS ( SELECT *, CASE WHEN DATEPART(WEEKDAY, order_date) = 7 THEN DATEADD(DAY, 2, order_date) WHEN DATEPART(WEEKDAY, order_date) = 1 THEN DATEADD(DAY, 1, order_date) ELSE order_date END AS Temp_date FROM customer_orders ), cte2 AS ( SELECT *, DATEDIFF(DAY, Temp_date, ship_date) AS days_difference, DATEDIFF(WEEK, Temp_date, ship_date) AS weeks_difference FROM cte ) select *,days_difference-2*weeks_difference as Business_days from cte2

Nice videos u make!🙏🏻 thank u

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

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.

Useful

Here is my solution my MYSQL: adding 7 day = Order_date + 7 Days of delivery date select *, case when dayname(order_date) ='Saturday' then date_add(order_date,interval 10 DAY) when dayname(order_date) in ('Sunday','Monday','Tuesday','Wednesday') then date_add(order_date,interval 9 DAY) when dayname(order_date) in ('Thursday','Friday') then date_add(order_date,interval 11 DAY) else order_date END business_day from customer_orders;

Hi Ankit, Table A Col1 col2 A. 1 B. 2 C. 3 D. 4 E. 5 Output: Col1 col2 B 1 A 2 D 3 E 4 E. 5 How to achieve this using SQL

Thank you sir ❤️❤️

select *, Datediff(day,order_date,ship_date) - 2*Datediff(week,order_date,ship_date)+2 business_days_added

declare @n int; set @n=3; with leaddate as (select mydate, lead(mydate,@n) over(order by mydate) as three_later_date from dates where datepart(weekday,mydate) not in(1,7) ) select mydate,three_later_date from leaddate where mydate='2024-03-26'

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

Here is my assignment. I have added 10 days in order_date column and calculated the actual business days to ship. select *, DATEADD(day, 10, order_date) as Added_ten_days, DATEDIFF(week, order_date, DATEADD(day, 10, order_date)) as Difference_of_week, DATEDIFF(day, order_date, DATEADD(day, 10, order_date)) as number_of_days_to_ship, DATEDIFF(day, order_date, DATEADD(day, 10, order_date)) - 2*DATEDIFF(week, order_date, DATEADD(day, 10, order_date)) as Actual_business_days_to_ship from customer_orders_five

sir can you please explain me in simple words the diffrence between count(*) and count(1) ?

I have a Q For Example in the table there is date filed and i want to fetch quarter from date filed it should start from financial quarter as Q1 like this how to get this ?

Please provide a query by which we can get 1st date of every month.

Hey Ankit, i am unable to calculate week in mysql using datediff. for week less than 7 days i am getting 0 if i divide datediff by 7. please help

@Ankit_Bansal Sir please provide solution to the assignment, I am getting confused

Can you pls give us the code for creating this table?

Hello @ankit bansal sir I am working on a project and imported data which contains datetime col(which has date and time)in text format I have tried everything to convert it to datetime format but its showing error everytime. Please enlighten me how to cast in correct way PS I am using mysql

What if days_to_ship = 4 and these 4 days are not weekends

Last like to 800 by me.😂😂

Adding days excluding Weekends Can you give us solution for the last question?

MYSQL users can use the following query: SELECT *, datediff(ship_date,order_date) AS days_to_ship ,WEEK(ship_date)-WEEK(order_date) AS weeks , datediff(ship_date,order_date) - 2*(WEEK(ship_date)-WEEK(order_date)) AS business_days_to_ship FROM customer_orders;

Where can i find the answer to that question ?

Please what can i do when i get this message "'DATE_PART' is not a recognized built-in function name."

this video is not included in any playlist.

Hello Ankit, Please provide the solution to the question asked by you.

Select dateadd(day,7,'2022-01-23')+2

Assignment question: Adding 5 business days from a given date: Select order_date,dateadd(day,5+(2*DATEDIFF(week,order_date,dateadd(day,5,order_date))),order_date) n_business_days from customers

MYSQL- Logic for adding given number of business days to the provided date .........select case when dayname(date_add(date_add('2022-12-29', INTERVAL 7 DAY),INTERVAL cast(FLOOR((datediff(date_add('2022-12-29', INTERVAL 7 DAY),'2022-12-29')/7)) as signed)*2 DAY)) IN( 'Saturday','Sunday') then date_add(date_add(date_add('2022-12-29', INTERVAL 7 DAY),INTERVAL cast(FLOOR((datediff(date_add('2022-12-29', INTERVAL 7 DAY),'2022-12-29')/7)) as signed)*2 DAY),Interval 2 DAY) else date_add(date_add('2022-12-29', INTERVAL 7 DAY),INTERVAL cast(FLOOR((datediff(date_add('2022-12-29', INTERVAL 7 DAY),'2022-12-29')/7)) as signed)*2 DAY) end Business_days

I did this as : declare @whatsdate date; set @whatsdate='2022-02-07' select case when datepart(weekday,@whatsdate) in (1,7) then dateadd(day,6,@whatsdate) else dateadd(day,7,@whatsdate) end

SELECT DATEADD ( DAY, CAST(2*DATEDIFF(WEEK,order_date,ship_date) AS INT), ship_date) FROM customer_orders;

Assignment Answer: Did it for 5 days, can we done for N number of days select getdate(),DATEADD(day,5,GETDATE()) as days_added,DATEDIFF(week,GETDATE(),DATEADD(day,5,GETDATE()))*2 as no_of_weekend_days,DATEADD(day,5 - DATEDIFF(week,GETDATE(),DATEADD(day,5,GETDATE()))*2,GETDATE()) as week_days

MySQL solution for the business days to ship problem SELECT *, DATEDIFF(ship_date,order_date) AS days_to_ship, DATEDIFF(ship_date,order_date) - 2*(week(ship_date)-week(order_date)) AS business_days_to_ship FROM amazon_orders ;