ADO - updating multiple rows based on dates 2

[asphyx9]

Hello,

I am trying to write an ADO update script that can update multiple rows within a table based on two dates. For example, the rows are all assigned dates, and I want to update the corresponding rows via dates. An example:

Add the text "booked" into all rows that have a date in between 6/1/2003 and 6/10/2003. How would I go about doing this in ADO?

 

[spazman]

UPDATE TableName SET TheFieldtoUpdate='Booked' Where DateFieldInTable BETWEEN '6/1/2003' AND '6/10/2003'

This should do it for you.

 

[asphyx9]

Thanks, this logic works for my application!

 

[clarkin]

I'd be careful when using date formats with ASP scripts - they can change/break according to the regional settings set for the machine where your web server is. I'm presuming you are using US date format there..

In this case it actually has to do with your SQL server (guessing that is your DB) machine's regional settings but the idea is the same: try and make your date strings as unambiguous as possible.

you could:

UPDATE TableName SET TheFieldtoUpdate='Booked' Where DateFieldInTable BETWEEN '1 Jun 2003' AND '10 Jun 2003'

or

UPDATE TableName SET TheFieldtoUpdate='Booked' Where DateFieldInTable BETWEEN '2003-06-01 00:00' AND '2003-06-10 00:00'

good luck


Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].

 

[asphyx9]

Thanks Clarkin,

the date format is definitely something to consider. I will typically use the 2003-06-01 00:00 format for my date/time data. Thanks for the helpful post!