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://noviway.com/

Xem thêm bài viết: https://noviway.com/category/cong-nghe

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

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

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

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

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

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

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

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

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

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

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

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

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

Leave a Comment