Tổng Hợp

DatePart, DateAdd and DateDiff functions in SQL Server Part 27




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/

Tổng Hợp
Hướng Dẫn Sử Dụng Phần Mềm Videoscribe ĐẦY ĐỦ nhất (P.1) | Cách làm video animation dạng tay vẽ
Tổng Hợp
Đột Kich Việt Nam Nơi Hội Tụ Những Hack"Cơ" Lầy Lội – Rùa Ngáo
Tổng Hợp
Windows 10 : Sọan thảo văn bản Wordpad & Notepad – Bài 4/5
  • 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.


  • Me MeMe Me

    Author Reply

    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!