DatePart, DateAdd and DateDiff functions in SQL Server Part 27

Công Nghệ



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://bus-rush.info/

Xem thêm bài viết khác: https://bus-rush.info/cong-nghe/

36 thoughts on “DatePart, DateAdd and DateDiff functions in SQL Server Part 27

  1. 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

  2. You are a gem. Explains Tricky concepts with such ease. Thanks for this complete video tutorial series.

  3. 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…

  4. select DATEADD(year, @years, @tempdate)

    how do we find month with this code? it gives a year-adding

  5. 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.

  6. 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 .

  7. 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#?

  8. select dateadd(yyyy,2010-1900,0) means why we use 1900 for what purpose
    pls give answer about that

  9. 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;

  10. 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

  11. 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.

  12. Outstanding !!! Your tutorial is really easy to understand, digest and develop a carrer.. Thank you

  13. 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

  14. 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.

  15. 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

  16. 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.

  17. 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.

  18. 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.

  19. Kudvenkat, you're one of the best teachers I've ever known. Simple, step by step explanation of topics. Easy to understand.
    Thanks.

  20. 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.

  21. 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?

Leave a Reply

Your email address will not be published. Required fields are marked *