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
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.
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 */ ;
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
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
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?
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
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)
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
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.
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'
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.
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
--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
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
if you want to see how to break down into smaller parts and approach then you should go through this series ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-jDx3BqrLmjk.html I am also going through the above series apart from this ..
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.
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;
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'
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
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 ?
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;
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 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
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;
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
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 ;