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
Slides
All SQL Server Text Articles
All SQL Server Slides
All Dot Net and SQL Server Tutorials in English
All Dot Net and SQL Server Tutorials in Arabic
Nguồn: https://vinhtrinh.com.vn
Xem thêm bài viết khác: https://vinhtrinh.com.vn/tong-hop/
Xem thêm Bài Viết:
- Mách bạn cách làm hoa ly thủ công bằng giấy xinh xắn siêu đơn giản
- Hướng dẫn trẻ mầm non cách vẽ xe đạp đơn giản nhất
- Đọc ngay cách lấy mật khẩu face của người khác nhanh và chi tiết nhất
- Bật mí địa chỉ in chuyển nhiệt chăn ga gối uy tín nhất Hà Nội
- Góc giải đáp: Ý nghĩa sim tam hoa 555 là gì? Có nên mua hay không?
You are a gem. Explains Tricky concepts with such ease. Thanks for this complete video tutorial series.
Yep that was hardest video up to now. Took me long to understand literally
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…
Bravo!! Well Done!
@Venkat : Wonder if your birthday is 10-08-1982
In the last 10 mins is boring and confusing it seems un necessary topic in last 10 minutes.
select DATEADD(year, @years, @tempdate)
how do we find month with this code? it gives a year-adding
Hi, do you have any idea or video where you can show us how to use a function "same period last year"if that exist in SQL, in order to get a a value and compare to the last year. thanks and regards.
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 .
please do a python video
Simply superb tutorial
What is the datatype of column DateOfBirth in that table.tblEmployees, is it date time?
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#?
select dateadd(yyyy,2010-1900,0) means why we use 1900 for what purpose
pls give answer about that
videos with every small explanation, but i am getting python udemy ads, model looks like crow
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;
When do you use SET and SELECT statements?
logic is quite complicated 😀
Venkat is phenomenal. I wish I could meet you.
Hi.. can you help me to find out continuously absent check in sql query in two or more table using date & time. my mail add is itkaushik@outlook.com
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
Why does not DATENAME(YEAR, '2018/01/01') RETURN 2018? It should return 'Two-Thousand Eighteen'
CREATE FUNCTION FN_CountAge(@DOB DATETIME)
RETURNS NVARCHAR(50)
AS
BEGIN
DECLARE @TEMPDATE DATETIME , @YEAR INT , @MONTH INT , @DAY INT
SELECT @TEMPDATE = @DOB
SELECT @YEAR = DATEDIFF(YEAR,@TEMPDATE,GETDATE()) –
CASE WHEN DATEPART(MONTH,@DOB) > DATEPART (MONTH,GETDATE())
OR DATEPART(MONTH,@DOB) = DATEPART(MONTH,GETDATE())
AND DATEPART(DAY,@DOB) = DATEPART(DAY,GETDATE()) THEN 1 ELSE 0 END
SELECT @TEMPDATE = DATEADD(YEAR,@YEAR,@TEMPDATE)
SELECT @MONTH = DATEDIFF(MONTH,@TEMPDATE,GETDATE()) –
CASE
WHEN DATEPART(DAY,@DOB) > DATEPART(DAY,GETDATE())
THEN 1 ELSE 0 END
SELECT @TEMPDATE = DATEADD(MONTH,@MONTH,@TEMPDATE)
SELECT @DAY = DATEDIFF(DAY,@TEMPDATE,GETDATE())
DECLARE @AGE NVARCHAR(50)
SET @AGE = CAST( @YEAR AS nvarchar(4)) +' Years '+ cast( @MONTH as nvarchar(4))+' Months '+ cast( @DAY as nvarchar(4))+' Days Old'
RETURN @AGE
END
Great Job. Very Clear and simple.
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.
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
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.
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.
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.
Kudvenkat, you're one of the best teachers I've ever known. Simple, step by step explanation of topics. Easy to understand.
Thanks.
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.
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?
EVEN THOUGH YOUR TUTORIALS ARE STILL VERY MUCH THE BEST
I REALLY DON'T UNDERSTAND WHY AND HOW YOU WOULD SAY THIS IS SIMPLE.
Brief and outstanding THANK YOU
Outstanding Sir GOD BLESS YOU
Wonderful job!