Post reply

Update only date against datetime field

  • March 12, 2010 at 7:12 am

    #386490

    Hi experts,

    i have an database date field in sql server 2005. i want to build an query to update only date against full datetime time should retain same... please show me the way asap...

    thanks & Regards,

    murali

  • March 12, 2010 at 7:35 am

    #1132696

    declare @x datetime -- this is your date time column or variable

    set @x = getdate() -- set it to current time value

    select @x -- display current value

    set @x = convert(char(10),@x,101) + ' 01:01:01' -- set time to a new value

    select @x --display new datetime value

    ---- sorry, I must be dyslexic. You wanted to change the date not the time:

    set @x = '2010-01-01 ' + convert(char(12),@x,114) -- set new date value, same time

    select @x -- display new value

    The probability of survival is inversely proportional to the angle of arrival.

  • March 12, 2010 at 7:43 am

    #1132707

    Have a similar solution using dateadd and datediff.

    declare @d datetime

    set @d = '2010-04-04'

    update dateupdate set [date] = DATEADD(dd, DATEDIFF(dd, [date], @d), [date]) where id = 1

    Regads,

    http://www.ms-sql-server.com

  • March 12, 2010 at 10:53 am

    #1132871

    Thanks Alot dude...:-)

Viewing 4 posts - 1 through 3 (of 3 total)

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