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

[@ritikajaiswal3824]

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

[@ankitbansal6]

Sure. Thank you 😊

[@My-Research]

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

[@ankitbansal6]

Welcome aboard!

[@CosmicKaBot]

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.

[@nishabansal2978]

Very useful videos with scenario, keep it up

[@ankitbansal6]

Thank you 😊

[@ashwanibhati1607]

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;

[@ls47295]

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

[@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.

[@Iampurankandpal]

Same here. I also found his channel on linkedin. A big thanks to you bro for such a great work you are doing.

[@ankitbansal6]

Thank you so much. You made my day 🙂

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

[@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;

[@rishav144]

perfect 🔥

[@adityeshchaturvedi6553]

Loved every piece of it. Crystal clear explanation!

[@ankitbansal6]

🙏

[@keifer7813]

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

[@ankitbansal6]

Good question. Assumption is order date and ship date will always be weekdays .

[@kanchankumar3355]

Helpful.. great explanation

[@ankitbansal6]

Thank you 😊

[@lokeshsharma-qy4vu]

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

[@mohammadmobashshir5378]

week diff will be 0 if it is in same week

[@gouravmalhotra8972]

Hi Ankit , thanks for the video , i was searching logic to add business days in a given date but was not able to find it. could you please help me if you have already commented the logic.

[@AbhishekKumar-bh6is]

You are doing great bro please post everyday one question answer

[@ankitbansal6]

Thank you 😊

[@akp7-7]

@@ankitbansal6please pin the create and insert statement. it will help others i got it from comments

[@idwtv534]

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

[@gauravsharma2223]

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

[@ankitbansal6]

Awesome 😊

[@sabyasachiadhikary7138]

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

[@ankitbansal6]

Keep watching

[@mohit3300]

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.

[@gajanantiwari6297]

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

[@ankitbansal6]

I am using SQL server

[@agraj5608]

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

[@ankitbansal6]

Looks good. did you try run it?

[@agraj5608]

No Ankit haven't run it. :) Was looking at the video late in the night while going to bed.

[@amanranjanverma]

The query is correct, but it will not work for one of the corner cases where: order_date(1st, Wednesday) and we are adding 7 business dates to it. Initially, it looks like it has one weekend so the shift will be by 2 days but originally this 2 day shift will lead to another 2-day shift, in total 4 days. 1st(wed) + 7 = 8th(Thrusday) -> 1 weekend in between so, 1st(wed) + 2 + 7 = 10th(Saturday) -> 1 more weekend so, 10th(Sat) + 2 = 12th(Monday) Python: stackoverflow.com/questions/12691551/add-n-business-days-to-a-given-date-ignoring-holidays-and-weekends-in-python add_business_date(datetime.date(2022,6,2), 7) Result: datetime.date(2022, 6, 13)

[@amanranjanverma]

@@ankitbansal6 do you have a solution video for the same?

[@pavitrashailaja850]

Nice videos u make!🙏🏻 thank u

[@ankitbansal6]

Thanks 🙏

[@2412_Sujoy_Das]

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

[@ankitbansal6]

The only condition is the data type of the column should be date or datetime or timestamp .

[@2412_Sujoy_Das]

@@ankitbansal6 ok

[@2412_Sujoy_Das]

ok @@ankitbansal6 sir,,,, Just one more thing...... I used a lag function (in Y-O-Y growth rate question in DataLemur website) and didn't use an order by inside the window. But to my surprise when I used the ORDER BY clause outside, it affected the window function. Is it possible or it happened due to something else?

[@ankitbansal6]

@@2412_Sujoy_Das not possible

[@ramyapotnuru5079]

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

[@ankitbansal6]

Have not provided the solution yet. Will plan to publish soon.

[@ratneshraj4653]

@@ankitbansal6 please provide the solution

[@ajitkumarraut4979]

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)

[@theraizadatalks14]

The Same logic I've implemented: declare @today_date date ,@start_date date set @today_date = '2022-01-23' Select @today_date as 'todayDate', dateadd(day,2*datediff(week,@today_date,dateadd(day,7,@today_date)) ,dateadd(day,7,@today_date)) as '7days_ahead_without_weekend'

[@manjitsharma3713]

How can I extract the current year from lets say a coulmn named order_date? So basically, like I want to fetch the product names which are ordered in the current year.

[@ankitbansal6]

Based on current date function

[@readname4698]

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

[@Ashu23200]

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

[@danish9191]

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;

[@sahilummat8555]

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

[@datascienceenthusiast3964]

if you want to see how to break down into smaller parts and approach then you should go through this series kzbin.info/www/bejne/oHXbZHWnp7GgoM0 I am also going through the above series apart from this ..

[@bharatghumoblog970]

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

[@ajichacko6803]

--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

[@swetasuman4498]

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

[@ankitbansal6]

Why?

[@avinashglpaccountancy999]

I find it wrong please correct me if not-- In days_to_ship column all days are divisible of 6 in that case it is a 6days week and sundays are not included then our assumtion wont work , what if days are less then week days, is it possible to find day of week is sunday and then removing the day

[@story_teller_Is]

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.

[@wanderwithsingh]

Agreeeee

[@aishashaik5667]

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

[@gokulsutar1696]

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 ?

[@ankitbansal6]

Datepart function

[@Time_Traveller_Dubai]

Thanks sir

[@kumarankit7433]

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

[@ankitbansal6]

Check it out kzbin.info/www/bejne/f3abf6ibntirfrs

[@kumarankit7433]

@@ankitbansal6 Thanks sir.. Now its clear..👍

[@vishnujatav6329]

Useful

[@yatinshekhar787]

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.

[@praneethveeramachaneni6672]

use timestampdiff() date function.

[@prateeksingh906]

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

[@PiyushKumar-tv6dr]

For the Age problem it shows years without considering the months are completed or not. Suppose if someone is going to be 27 by Dec but it will show show 27 years even in August also. That's not right

[@adarshtrivedi6822]

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

[@ankitbansal6]

Show me data and error

[@adarshtrivedi6822]

@@ankitbansal6 the format for date time is '3/17/2019 5:39' , I am trying to update with this query update `sales`.`sales_march_2019` set `order date` =STR_TO_DATE( `order date`, '%mm/%dd/%YYYY %hh:%mm:%ss') showing error-- Incorrect datetime value: '3/17/2019 5:39' for function str_to_date, also one of the query was showing null values in order date col

[@Blessy_7777]

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

[@Freakouts_and_found_Insane]

Thank you sir ❤️❤️

[@Ankit-rv2my]

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'

[@manojtripathi6144]

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

[@yashikachugh4198]

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

[@akp7-7]

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

[@ankitbansal6]

Try this select week('2010-11-18') - week ('2010-10-18');

[@akp7-7]

@@ankitbansal6 wow it worked! thanks

[@rashmipatil-c6b]

please provide the solution ankit for the adding business days am not getting it

[@ajaylandge5356]

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

[@SuperMohit95]

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

[@mohammadabdullahansari6314]

create table amazon_orders ( order_id integer, customer_id integer, order_date date, ship_date date ); insert into amazon_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 amazon_orders;

[@AlwaysBeTactful]

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

[@ankitbansal6]

Which database?

[@AlwaysBeTactful]

@@ankitbansal6 I think it's 2023 SSMS

[@ririraman7]

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

[@gameply347]

how does it works .

[@gameply347]

Where can i find the answer to that question ?

[@ritikajaiswal3824]

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

[@ankitbansal6]

Yes i will make a video on it

[@tejaswidalavi9269]

@@ankitbansal6 Can you share the link to verify

[@sanilkumarbarik9151]

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

[@sandeepupreti-w4f]

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

[@mohit3300]

this video is not included in any playlist.

[@ankitbansal6]

Will add

[@sanskarvarshney6114]

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

[@ankitbansal6]

Sure

[@ashwanibhati1607]

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;

[@Hkumar_new]

Last like to 800 by me.😂😂

[@ankitbansal6]

Thank you 🙏

[@vyabinivenkatesan7839]

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

[@sudarshanthota4444]

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

[@ankitbansal6]

You can't add +2 simply . you may have 2 consider more than 1 weekend or no weekend..

[@mmmf23]

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

[@ankitbansal6]

What's the logic here ?

[@ririraman7]

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

[@litheshraju5207]

hi in above solution if the day is in sunday then by adding 6 to sunday date this will result in saturday date. if worng please correct me

[@rishabhkjha92]

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

[@arthurmorgan9010]

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

[@SuperMohit95]

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 ;