update datetime column

Posted on 2012-08-23
Last Modified: 2012-08-24
I've got to update a datetime field to another column of varchar but not for the rows that have the current date say 2012-08-23

TodaysDate varchar(1000), null
insertDate [datetime] NULL  DEFAULT getdate()

example data
TodaysDate = 2012-07-24
insertDate = 2012-08-23 12:11:23.613

desired result for above should be:
insertDate = 2012-07-24 12:11:23.613

i tried this but no luck
update table set insertDate = TodaysDate from table where insertDate <> '2012-08-23'
Question by:fwstealer
    LVL 75

    Assisted Solution

    by:Aneesh Retnakaran
    update table
    set insertDate = TodaysDate
    from table
    where insertDate >= '2012-08-23'
    and insertDate < '2012-08-24'

    Author Comment

    well that took care of the date but set the time portion to 00:00:00.000 so it didn't retain the times.
    LVL 68

    Accepted Solution

    update table
    set insertDate = DATEADD(DAY, DATEDIFF(DAY, @insertdate, @Todaysdate), @insertDate)from table
        insertDate >= '20120823' and
        insertDate < '20120824'

    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
    Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now