update datetime column

Posted on 2012-08-23
Medium Priority
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
Aneesh Retnakaran earned 1000 total points
ID: 38326605
update table
set insertDate = TodaysDate
from table
where insertDate >= '2012-08-23'
and insertDate < '2012-08-24'

Author Comment

ID: 38326652
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 70

Accepted Solution

Scott Pletcher earned 1000 total points
ID: 38327264
update table
set insertDate = DATEADD(DAY, DATEDIFF(DAY, @insertdate, @Todaysdate), @insertDate)from table
    insertDate >= '20120823' and
    insertDate < '20120824'

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Integration Management Part 2
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

580 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