UPDATE only records that were created after a date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

UPDATE only records that were created after a date

Author

Topic

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2007-09-05 : 16:54:17

I have a text file that I am importing into my database.

Within this textfile there is a field named "Order_Date". Date Format is like this: 20070905

I want to run this DTS once a day, updating only the records that were created on "today's" date.

For example something like this:

UPDATE NAME
WHERE Order_Date = ????? (I want a variable that is todays date in 20070905 format)

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-05 : 18:29:47

where order_date = convert(varchar(8),getdate(),112)

Future guru in the making.

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2007-09-05 : 18:52:21

Great! Of course one question leads to more... :)

How do I modify that to cover yesterdays date. I know there is probably a "-1" to insert, but I dont know where. Also, I want to change this statement to UPDATE.... any help?

DELETE FROM DOB
INSERT INTO DOB (FCN, DOB_YEAR, DOB_MO, DOB_DAY)
SELECT FCN, DOB_YEAR, DOB_MO, DOB_DAY
FROM DATAGRAB WHERE ORDER_DATE = convert(varchar(8), getdate(), 112)

Obviously this statement deletes all the data in my table and adds the new info. I want to update the table with the records from yesterday's date.

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-05 : 19:25:48

Well, WHERE order_date = convert(varchar(8),(getdate()-1),112) will get you yesterday specifically.
WHERE order_date >= convert(varchar(8),(getdate()-1),112)
will get you yesterday and today.

As far as the update, are you wanting to append to the existing data or replace data on a fixed field?

Future guru in the making.

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2007-09-05 : 19:46:25

I want to append to the existing data. Thanks so much!

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-05 : 19:59:33