Post reply

Trying to understand Datepart and dateadd

  • July 20, 2009 at 8:14 am

    #208485

    I am trying to read a strored proc written by a programmer before me. I have attached the following code. could some one help me to understand what the -1 is for in the Datepart(hh,@adt_start_date) * -1 part of the following code? Any help is greatly appreciated!

    Thanks in advance!

    Shree

    DECLARE @adt_date datetime

    DECLARE @adt_start_date datetime

    SELECT @adt_start_date = getdate()

    SELECT @adt_start_date = dateadd(mi, Datepart(mi,@adt_start_date) * -1, @adt_start_date)

    SELECT @adt_start_date = dateadd(hh, Datepart(hh,@adt_start_date) * -1, @adt_start_date)

    SELECT @adt_date = dateadd(dd,1,@adt_start_date)

    print @adt_date

    print @adt_start_date

  • July 20, 2009 at 8:23 am

    #1026975

    He's subtracting the hours and minutes from the present to 'floor' it ie get the midnight datetime of today.

    It better achieved by 'DATEDIFF(DAY,0,@Date)', that accounts for second and milliseconds which your code doesnt.

    Change the Print's to selects and you will see what i mean


    Clear Sky SQL
    My Blog[/url]

  • July 20, 2009 at 8:38 am

    #1026989

    The -1 is subtracting the hours and minutes portion of the @adt_start_date, so you're basically left with that date at midnight. The SQL datetime datatype can be represented as a number where the portion left of the decimal is the date and right of the decimal is the time.

    I generally use this expression when I want to drop the time portion of a date:

    select cast(floor(cast(getdate() as float)) as datetime)

    Ed

  • July 20, 2009 at 8:38 am

    #1026988

    Oh...I see what you mean. I can now figure out what the rest of the stored proc is doing based on these dates. Thanks a lot!

    Shree

  • July 20, 2009 at 8:39 am

    #1026993

    That is great info for a newbie like me! Thanks so much for helping out!

    Shree

  • July 20, 2009 at 8:46 am

    #1027003

    This will also "truncate" the time portion of a datetime value to midnight:

    select dateadd(dd, datediff(dd, 0, getdate()), 0)

  • July 21, 2009 at 3:55 pm

    #1027976

    This doesn't address your question directly, but it might help with your understanding of DateTimes

    http://www.sql-server-performance.com/articles/dev/datetime_datatype_p1.aspx

    http://www.sqlteam.com/article/datediff-function-demystified

  • July 24, 2009 at 4:41 am

    #1029549

    this will also work if u dont like 0's 😉

    select dateadd(dd, datediff(dd, 1, getdate()), 1)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply