Post reply

Offset in the date-time inserted while insertion

  • January 22, 2009 at 7:30 am

    #130321

    When i try to update a date into a table whose datatype is SMALLDATETIME, the time inserted has an offset of 3 hrs.

    eg:

    Date to be inserted -> 'Jan 01 2009 1:00:00:000AM'

    Date inserted -> 'Jan 01 2009 4:00:00:000AM'

    Why is this? How can this be prevented? I want the time to be 0:00:000AM irrespective of the time in the 'date to be inserted'

  • January 22, 2009 at 7:48 am

    #931178

    You could try this:

    select convert(datetime,convert(varchar(10),getdate(),101))

    When you use smalldatetime, seconds are rounded off to the nearest minute.

  • January 22, 2009 at 8:01 am

    #931187

    Here is another solution:

    SELECT DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

    This one doesn't do any conversions it simply does Math 🙂

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • January 22, 2009 at 8:22 pm

    #931602

    Thanks. But, I tired all these options and still the date entered into the table has a 3hr offset.

    With your suggestion i accept that the date being entered would be something like 01 01 09 00:00:0000 (dd mm yy hh mm ss). But when i check the table, i see the date entered as 01 01 09 03:00:0000

    Help pls 🙁

  • January 22, 2009 at 9:15 pm

    #931618

    Is there, by any chance, a trigger on the table that trys to "normalize" time zones?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 22, 2009 at 9:19 pm

    #931620

    I'm pretty sure that there are no such triggers

  • January 22, 2009 at 9:44 pm

    #931627

    To quote a good friend of mine... "A Developer must not guess... a Developer must KNOW." Be sure and take a look. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 22, 2009 at 10:33 pm

    #931635

    Oops... learnt a lesson. There was a trigger 🙁

    Thanks man

  • January 22, 2009 at 11:50 pm

    #931648

    It had to be... nothing else would do it. Thanks for the honest feedback.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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