DatePart, DateAdd and DateDiff functions in SQL Server Part 27
HTML-код
- Опубликовано: 31 авг 2012
- In this video we will learn about builtin date time system functions in sql server. DatePart, DateAdd and DateDiff functions in SQL Server will be discussed along with a real time example of using these functions.
Text version of the video
csharp-video-tutorials.blogspo...
Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our RUclips channel. Hope you can help.
/ @aarvikitchen5572
Slides
csharp-video-tutorials.blogspo...
All SQL Server Text Articles
csharp-video-tutorials.blogspo...
All SQL Server Slides
csharp-video-tutorials.blogspo...
All Dot Net and SQL Server Tutorials in English
ruclips.net/user/kudvenka...
All Dot Net and SQL Server Tutorials in Arabic
/ kudvenkatarabic
I felt the need to comment because you really helped me with your tutorials!
Thank you, Venkat, for all the effort you make to present programming in such an easy way, no matter how noob somebody may be.
You are the best teacher I could find on youtube. Keep up the good work! Hope it pays back...
Hello Maria - Thank you very much for the feedback. Means a lot. I have included all the sql tutorial videos, slides and text articles in sequence on the following page. Hope you will find it handy.
www.pragimtech.com/courses/sql-server-tutorial-for-beginners/
When you have some time, can you please leave your rating and valuable feedback on the reviews tab. It really helps us. Thank you. Good luck and all the very best with everything you are doing.
Sir, you simply deserve heaven. Outstanding tutorial.
Your videos are so easy to understand that I completed all your videos in this playlist in 3 days by playing it at X1.5 speed.
I only had 5 days before my job starts and i had no idea of SQL Server.
Now, I am really confident to attend my job.
Thank You Venkat from my bottom of my heart ❤️
Kudvenkat, you're one of the best teachers I've ever known. Simple, step by step explanation of topics. Easy to understand.
Thanks.
You are a gem. Explains Tricky concepts with such ease. Thanks for this complete video tutorial series.
This may be little silly,but I love the calmness in your videos. Please don't change this . Extremely thankful for sharing your knowledge with us.
Years gone... But this video; in the first few minutes, I was able to spot what I was missing. Thank you. Really happy. #2020
what's wrong with teh people that downvoted this video? not only this video, but the entire series is simply awesome
Thank you very much for taking time to give feedback. I upload training videos on a daily basis. If you want receive email alerts when new videos are uploaded, please feel free to subscribe to my youtube channel.
If you have problems understanding the DATEDIFF, keep in mind these four things:
1) It returns as integer.
2) It counts how many times you have to cross a line (from one year to another etc.)
3) It doesn't care about greater accuracy than you pass in as the first parameter. Year 2017 is year 2017, no matter whether it is January first or December 31st. So between 1.1.2017 and 31.12.2017 you are in the same year, datediff in years is zero, because you are essentially passing as parameters XX.XX.2017 and XX.XX.2017. And from 31.12.2016 to 1.1.2018 you have to cross two lines (to 2017 and then to 2018), the datediff returns two.
4) The +/- mark just tells whether you are going forward or backwards in time.
At least for me it took a while to understand the function, but with these four points, I think it should become pretty simple.
Arto Kilponen +1 for pointing out datediff counts when u cross the boundary for specified parameter
Excellent. Probably the best tutorial. Thank you very much sir..
Thank you very much, it was an excellent tutorial! Even the more difficult part was digested :-) I do not believe in copy-paste learning, I do not believe, that you can learn well, if you just copy the part that is too complicated for you. If you write it by yourself, you learn more.
Outstanding !!! Your tutorial is really easy to understand, digest and develop a carrer.. Thank you
God bless you venkat bro.
sir ur best teacher at this earth,salute u
Yep that was hardest video up to now. Took me long to understand literally
much love from Florida thank you for your wonderful videos!
Hi venkat.
You are great teacher.
I learnt from your tutorials alot.
I have your all tutorials. C sharp sql asp mvc entity framework javascript bootstrap and much more.
I love you so much.
Thank you for another very useful session on Dates and Functions. I am surely benefited a lot by this.
Great tutorial!! Simple and efficient 👍
Simply exceptional...I am speechless...thank you for this "SQL version of C# coffee machine" :D
Thank you very much for taking time to give feedback. This means a lot. I am very glad you found the videos useful.
I have organised all the Dot Net & SQL Server videos in to playlists, which could be useful to you
ruclips.net/user/kudvenkatplaylists?view=1&sort=dd
If you need DVDs or to download all the videos for offline viewing please visit
www.pragimtech.com/kudvenkat_dvd.aspx
Slides and Text Version of the videos can be found on my blog
csharp-video-tutorials.blogspot.com
Tips to effectively use my youtube channel.
ruclips.net/video/y780MwhY70s/видео.html
If you want to receive email alerts, when new videos are uploaded, please subscribe to my youtube channel.
ruclips.net/user/kudvenkat
If you like these videos, please click on the THUMBS UP button below the video.
May I ask you for a favor. I want these tutorials to be helpful for as many people as possible. Please share the link with your friends and family who you think would also benefit from them.
Good Luck
Venkat
Thank u very very much Venkat. Its a really great work u have done!
Brief and outstanding THANK YOU
Thank you so much, giving us your valuable time.excellent GURU.
Vankat thank you despite the fact you wrote this 5 years ago and I'm a bit late you deserve congratulations and thanks for this lesson and this series, Raph.
EVEN THOUGH YOUR TUTORIALS ARE STILL VERY MUCH THE BEST
Outstanding Sir GOD BLESS YOU
I am following all your video and I love them, thank you
what a lecture! i learned so many things from this one
wonderful....its beyond perfect
it would be great if the codes could be copied for future reference for brginners
as I understood well but couldnt try immediately for practise
you are beauty, sir. Your way of teaching is amazing and very easy to understand.
Bravo!! Well Done!
Wonderful job!
Awesome lesson as always.
Simply superb tutorial
You are awesome, Venkat. Extremely helpful. Thanks so much!
Rewatched.
Thanks
Nice tutorial
I was playing around with this example. obviously need to do all this calculation is bcoz datediff calculates when u cross the boundary of specified parameter. Now if you directly subtract 2 date times what u get is an Interval (calculated as year-month / day-time) I.e the time lapse betn 2 dates represented in one of the above format
Let's say there is difference of 36 years 11 months 29 days 21 hrs and 46 mins betn datetime1 and datetime2 if u subtract 2 datetimes what u will get is 1936-11-29 21:46:00:000 (note: year 1900 is default value) so to get time lapse betn any 2 dates what u can do is
SELECT ID, Name, DateOfBirth, (GETDATE()-DateOfBirth) as Interval,
CONVERT(varchar(10), DATEDIFF(YEAR, 0,GETDATE()-DateOfBirth))+ ' Years '
+CONVERT(varchar(10),DATEPART(MONTH,GETDATE()-DateOfBirth))+ ' Months '
+CONVERT(varchar(10),DATEPART(DAY, GETDATE()-DateOfBirth))+ ' Days' as Age
FROM tblEmployeeDOB
Hope this helps
Best CONTENT on SQL
Really gr8 video session
very very nice...
Revisiting
Thanks
Thank you very much ❤️
Superb brother
Nice, thanks sir
Thanks guruji
No doubt that this is the best tutorial and well organized.
@kudvenkat - Could you please also help me with exercise questions with answers to practice query formation? please if you have it.
Can someone tell me the purpose of Venkat adding the year and month back into the @tempdate?
It is for getting a new value out of @DOB=> it has a reference to @tempdate, then @tempdate recieves the value and sets it as a new sustomized value for avoiding errors in culculating, I guess
good tutorial
Venkat is phenomenal. I wish I could meet you.
Venkat Sir, Do we not have a direct function to calculate AGE in SQL ?Example: AGE (DOB)
Sir I was trying to implement the logic which you explained in this particular video, But when i am executing the the function it is giving me the following error..
"Arithmetic overflow error converting expression to data type nvarchar."
Wow.. how about calculations on absent and overtime?
select dateadd(yyyy,2010-1900,0) means why we use 1900 for what purpose
pls give answer about that
Hi. it is useful. but l encounter problem with Arithmetic overflow error converting expression to data type nvarchar.
in this case change nvarchar to int or another when create declare and set?
Hi Venkat, To calculate the difference between two dates where even though the difference was 2 months it showed 1 year can we not use month as one of the parameters instead of using case statement. Please advise.
I really like this channel. BTW The example in this video is quite complicate, can anyone explain me that in the real world application, should we move this complicate process from SQL to C#?
Hello Vincat brother, I really learn alot , I am searching Azure learning if available kindly let me know.
In one of the video u said that return always return int value so how is it that in function it can return nvarchar?
hi Sir .i am trying to find 'working with variables' video but i couldnt.can you tell me which video i have to watch? thank you
So lets suppose i got date diff on days and then divide it with 356 or 12 to get month and year wont that be easier?
Hello Venkat,
I have database ,Date_Time , ProcessVal1, ProcessVal2, ProcessVal3 . I want to filter out data by minute and Hourly .
For report generation function. Can you please let me know . How i can do this.
When I am executing function dbo.fnComputeAge in the end it is showing an error as 'Arithmetic overflow error converting expression to data type nvarchar.'
Please tell me whats wrong with this
Pls tell me one thing. How did you get so good at sql?
Same question was asked to my friend in interview
Great tutorial Venkat. Could you please provide the URL of you blog. I can't find it.
Thanks in Advance.
+Awais Khokhar Here is the link to my blog.
csharp-video-tutorials.blogspot.com
What is the datatype of column DateOfBirth in that table.tblEmployees, is it date time?
How do you deal with an interval of Half-yearly?
if we pass future date to tempvar but return age is not in currect format in above query .
what is the purpose of @tempdate here?
how to find the difference between two dates in a same column using sql
logic is quite complicated :D
When do you use SET and SELECT statements?
SET is the ANSI standard way of assigning values to variables, and SELECT is not. But you can use SELECT to assign values to more than one variable at a time. SET allows you to assign data to only one variable at a time. So that in performance is where SELECT will be a winner.
how to find difference between two date columns in years, moths and days?
Your explanation is very good. One quick doubt, I can see that you have mentioned that days will be nvarchar(2) while converting from Integer to String, but I believe it should be nvarchar(3), please correct me if I my understanding is wrong
Dates have only 2 digits at max
For example, 31 December ,,, at max we can have only 2 digits... not more than 2
Sir your tutorials are so good !! sir littly i am confuse in
select @years=datediff(YEAR,@tepdate,GETDATE())-case when
(month(@dob)>month(GETDATE()) or (MONTH(@dob)=MONTH(GETDATE()) and (DAY(@dob)>DAY(GETDATE()))))then 1 else 0 end
this statement or (MONTH(@dob)=MONTH(GETDATE() ... Sir why you use this.
we are adding calculated years to the dob to avoid calculating months from begging..because in this logic we are calculating years,months and days separately..try to play with this query by commenting the dateadd part in this logic, for sure u will get the clarity
maja aaya
Hi everyone, my output shows as follows:
40 Years 9 Months 14634 Days old
Is there a way to filter this in this way?
if days > 30
then month + 1
and if month > 12
then year + 1
Thanks in advance!
why we are using 2 parameter ,@tempdate and @dob,and why we use select before @tempdate
Why we need to use @tempdate here? Why it is placed after case statement ? Why didn’t you use case Statement after calculating @days?
please do a python video
Hello, I was trying to do something like SELECT DATEADD(YEAR,-7, YEAR(GETDATE())). According to me, it should subtract 7 years from the current year but it's giving me result like 1898-07-15 00:00:00.000 . Can you explain this?
Buddy it works like this ::
SELECT DATEADD(YEAR, -7, GETDATE())
I have a doubt why are we subtracting one in case statement. For year month and days i am not able to grasp it can someone reply me .
Memeorandum of Understanding thanks so much for taking your time to reply now i understood it clearly
select DATEADD(year, @years, @tempdate)
how do we find month with this code? it gives a year-adding
we are adding calculated years to the dob to avoid calculating months from begging..because in this logic we are calculating years,months and days separately..try to play with this query by commenting the dateadd part in this logic, for sure u will get the clarity
To Understand Step by Step using print statement
--select datediff(year,'06/06/2019','06/05/2020')
Declare @dob datetime , @tempdate datetime,@years int,@months int,@days int
set @dob = '12/02/1984'
select @tempdate = @dob
print(@tempdate)
select @years = datediff(year,@dob,GETDATE())-
case
when (month(@dob) > month(getdate())) OR
(month(@dob) = month(getdate()) AND day(@dob) > day(getdate()))
Then 1 else 0
END
print(@years) -- 35 years
print(@tempdate)
select @tempdate = dateadd(year,@years,@tempdate)
print(@tempdate) --35 years added to 1984 means 1984+35=2019 i.e Dec 2 2019
select @months = Datediff(month,@tempdate,getdate())-
case
when day(@dob) > day(getdate())
then 1 else 0
end
print(@months) --6 months difference b/c my getdate() is 06/05/2020
select @tempdate = dateadd(MONTH,@months,@tempdate)
print(@tempdate) -- 6 months added to Dec 2 2019
select @days = DATEDIFF(DAY,@tempdate,GETDATE())
print(@days) --3 days
select @years as year, @months as month,@days as days
How about this query?
declare @d1 date = '2010-12-31', @d2 date = '2011-01-01';
declare @ds int = datediff(day, @d1, @d2);
select @ds/365 as years, (@ds%365)/12 as months, @ds%12 days;
would you please tell me why @ds%12 is used as days
I REALLY DON'T UNDERSTAND WHY AND HOW YOU WOULD SAY THIS IS SIMPLE.
Remember that you shouldn't try to swallow the entire code at once (especially when you are studying these things) but instead make sure that you understand each and every statement that what they do. Encapsulating the code properly helps a lot with this and an example of that is the function created at the end.
27th part video is very confusing example . I watch many times but I am not able to understand.. still trying to understand
Video was blurred.
videos with every small explanation, but i am getting python udemy ads, model looks like crow
Hi, I am using netbeans and wampserver, it says function DATEADD does not exist whenever I run it, why is that?
Thanks in advance!
Mat Allah reward you sir 😭😭😭❣️❣️❣️❣️
I was rejected by my crush on September 19th, 2012. 😹😹
In the last 10 mins is boring and confusing it seems un necessary topic in last 10 minutes.
-select datediff(YEAR,'2020-09-02',GETDATE())---can anyone explain please these case statement
SELECT @years = DATEDIFF(YEAR, @tempdate, GETDATE()) -CASE WHEN (MONTH(@DOB) > MONTH(GETDATE()))
OR (MONTH(@DOB) = MONTH(GETDATE()) AND
DAY(@DOB) > DAY(GETDATE())) THEN 1 ELSE 0 END