Updating a SQL 2000 Table, correct syntax, not updating

[antivirus22]

I'm trying to update a table called Processed in my database to set exportdate to 1/1/1901 where the
exportdate = 1/1/1900 and also date < 20021124.
I returned all rows in the table and then did a SQL statement from there.

I coded it :
UPDATE Processed
SET exportdate = '01/01/1901'
WHERE (exportdate='01/01/1900') AND ([date] <20021124)

 

[SQLBill]

What datatype are exportdate and date?

-SQLBill

 

[antivirus22]

Exportdate is datetime and date is an integer

 

[SQLBill]

Ah....datetime is just that the date AND time. So, your where clause is looking for exportdate to equal 01/01/1900 and it can never do that. Why not? Because exportdate also has a time with the date and you aren't looking for that.

Try this for better understanding:

Select max(exportdate)
from processed

Look at what it returns....DATE and TIME.

Try this:

select convert(char(10),max(exportdate),100) as exportdate
from processed

It should only return the date.

Check out Books OnLine (BOL), use Index and search for CONVERT. Then try out the format that best suits your needs.

I'll play with it some and see if I can come up with a solution.

-SQLBill

 

[antivirus22]

The exportdate field is stored with only dates such as
01/01/1901 . I dont know why its set up as datetime datatype.
When i ran that command max(exportdate) it only brought up one record which was 12/11/2002

 

[tlbroadbent]

The date only can be stored in a datetime column so you can very easily have a datetime column = '01/01/1900'. There is no separate date or time data type in SQL Server.

Have you tried a Select query based on the criteria to see what results would be returned?

Select * From Processed
WHERE exportdate = '01/01/1900' AND [date] < 20021124
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[antivirus22]

Yeah, i tried a select query just to make sure there actually are instances of the specified condition.
Its strange. Its executing but the data remains unchanged and not updated.

any other ideas?

thanks

 

[SQLBill]

Terry,

Thanks for correcting me. I was under the belief that datetime has both and if a time isn't provided then it defaults to 00:00:00.000. I learn something every day.

Antivirus22,

Sorry for giving you bad info.

-SQLBill

 

[antivirus22]

Thank you....

Do you have any ideas why this is not updating the records?

 

[antivirus22]

Ok now the update gave me a time out error
[microsoft][odbc sql server driver] timeout expired

 

[tlbroadbent]

Bill,

The time does default to 00:00:00.000 or zero so using just the date will suffice for a search.

antivirus22,

How many recorrds are you attempting to update? It sounds like the query is not completing therefore the rows are not updated. No updates are committed to the databse until the statement completes. Are you executing this query from SQL Query Analyzer or from a different client.

You may want to limit the number of rows updated per batch and execute multiple update batches. Here is one method that I use to perform mass updates and avoid the problem of timeouts or exceedingly large log files. This method will run much faster than attempting to update millions of rows in one statement.
[tt]
--declare and initialize rowcount variable
Declare @rc int
Set @rc=10000

--set rowcount to update maximum of 10K rows
Set Rowcount 10000

--loop until no more rows remain to update
While @rc=10000
Begin
Begin Transaction

UPDATE Processed With (tablockx, holdlock)
SET exportdate = '01/01/1901'
WHERE exportdate='01/01/1900'
AND [date] <20021124

Set @rc=@@rowcount

Commit
End

--reset the rowcount
Set rowcount 0 Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[antivirus22]

Awesome..
THank you so much,
it worked