Date Functions in SQL | Interview Questions on DATE Functions | DATEPART, DATEADD,DATEDIFF Functions
Вставка
- Опубліковано 22 січ 2022
- 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
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 */
;
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
Sure. Thank you 😊
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;
Loved every piece of it.
Crystal clear explanation!
🙏
Randomly found your channel super explanation.
You are The King 👑 in SQL.
Welcome aboard!
Very useful videos with scenario, keep it up
Thank you 😊
Bro you are a GEM. thank you for making these videos.
i solved some of the trickiest questions only after watching your videos.🤘🤘
Awesome 😊
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.
Same here. I also found his channel on linkedin. A big thanks to you bro for such a great work you are doing.
Thank you so much. You made my day 🙂
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.
Helpful.. great explanation
Thank you 😊
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.
Nice videos u make!🙏🏻 thank u
Thanks 🙏
Thank you sir ❤️❤️
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
You are doing great bro please post everyday one question answer
Thank you 😊
@@ankitbansal6please pin the create and insert statement. it will help others i got it from comments
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
HI Ankit, Can you please let us know if it is in weekend how to proceed further?
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
Good question. Assumption is order date and ship date will always be weekdays .
Thank you very much Ankit sir for the video , Really helpful.
Keep watching
Useful
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;
perfect 🔥
Hi @ankitbansal, these functions dont work in Oracle. It is only valid in mysql. But still Thanks for clearing the doubts. Really helpful
I am using SQL server
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
ua-cam.com/video/jDx3BqrLmjk/v-deo.html
I am also going through the above series apart from this ..
second way :
select
case when datepart(weekday,getdate()) in (1,7) then dateadd(day,6,getdate()) else dateadd(day,7,getdate()) end
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
week diff will be 0 if it is in same week
Hi Ankit thanks for this video
may I know in which video you provided the solution
Have not provided the solution yet. Will plan to publish soon.
@@ankitbansal6 please provide the solution
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)
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'
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
Looks good. did you try run it?
No Ankit haven't run it.
:)
Was looking at the video late in the night while going to bed.
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)
@@ankitbansal6 do you have a solution video for the same?
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
Please provide a query by which we can get 1st date of every month.
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.
use timestampdiff() date function.
what if the difference between order date and ship date are 3days? Then the business days calculation will fail
Why?
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;
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
Show me data and error
@@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
Sir, just a small Question.....
Do these functions work only if the dates are in YYYY-MM-DD format???
The only condition is the data type of the column should be date or datetime or timestamp .
@@ankitbansal6 ok
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?
@@2412_Sujoy_Das not possible
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
Try this
select week('2010-11-18') - week ('2010-10-18');
@@ankitbansal6 wow it worked! thanks
@Ankit_Bansal Sir please provide solution to the assignment, I am getting confused
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
What's the logic here ?
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 ?
Datepart function
Thanks sir
sir can you please explain me in simple words the diffrence between count(*) and count(1) ?
Check it out
ua-cam.com/video/IE8NueisxHY/v-deo.html
@@ankitbansal6 Thanks sir.. Now its clear..👍
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.
Agreeeee
select case when date_part(dayofweek,sysdate-4) >=0 and date_part(dayofweek,sysdate-4)
Adding days excluding Weekends
Can you give us solution for the last question?
Yes i will make a video on it
Can you pls give us the code for creating this table?
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;
What if days_to_ship = 4 and these 4 days are not weekends
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'
Hello Ankit,
Please provide the solution to the question asked by you.
Sure
Please what can i do when i get this message "'DATE_PART' is not a recognized built-in function name."
Which database?
@@ankitbansal6 I think it's 2023 SSMS
select *,
Datediff(day,order_date,ship_date) - 2*Datediff(week,order_date,ship_date)+2 business_days_added
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
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
--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
Select dateadd(day,7,'2022-01-23')+2
You can't add +2 simply . you may have 2 consider more than 1 weekend or no weekend..
this video is not included in any playlist.
Will add
Last like to 800 by me.😂😂
Thank you 🙏
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
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
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;
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
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
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
;