Post reply

Dropping the seconds or minutes from datetime

  • September 29, 2005 at 4:53 am

    #168535

    I need to update based on the date to the minute or hour only (dropping seconds or minutes) and this does not seem to be easy to do in Sql Server.
     
    In Oracle it would be something like
    where
    to_date(fieldname,'YYYY-MM-DD HH24:MM') = linkfield in other table 
     
    This has to update tens of millions of lines everyday and will be used in a join so I am trying to avoid putting date parts together.
     
    Is there a way to do this?

  • September 29, 2005 at 6:26 am

    #593987

    Hi,

    You can use Select Convert(varchar(10),Field,Style)

    The Style may be US Datetime, or UK Date time, if you want to know more on this, you can refer BOL.

  • September 29, 2005 at 6:29 am

    #593990

    There is no style for what i need -

    Are you saying that adjsuting the length of the varchar will effectively drop seconds or hours for the purposes of the link?

  • September 29, 2005 at 6:31 am

    #593991

    That worked before (trimming) .

  • September 30, 2005 at 9:00 am

    #594350

    Using date-related functions is generally much faster than conversion to string.

     

    DECLARE @d datetime

    SET @d = GETDATE()

    SELECT "current" = @d,

      "hours" = DATEADD(hh, DATEDIFF(hh, 0, @d), 0),

      "minutes" = DATEADD(mi, DATEDIFF(mi, 0, @d), 0)

  • October 1, 2005 at 7:33 pm

    #594462

    Quick and easy change your datatype to SmallDatetime it has less resolution so you will not get seconds.  Try the link below for more info.  Hope this helps.

    http://www.stanford.edu/~bsuter/sql-datecomputations.html

    Kind regards.

    Gift Peddie

     

     

    Kind regards,
    Gift Peddie

Viewing 6 posts - 1 through 5 (of 5 total)

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