• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 382
  • Last Modified:

MSSQL - Query to update a boolean based on Less than Date

Hi Everyone

Trying to update a table called 'tblPerson' to close off users who are inactive from a specified date.

Column 1: 'createDate'
Boolean to update: 'closed' (by default set to 0) False (meaning the account isn't closed)

I want to query the database to close from a certain date specified below, anything older than that I want to set 'closed' to = 1

This is what I have come up with below, but doesn't seem to work

 
UPDATE tblPerson
SET closed = 1
WHERE createDate < 19072010 0:00:00

Open in new window



If anyone could shed some light, or help out with the code it would be much appreciated

Regards

Alex
0
lxboston
Asked:
lxboston
  • 5
  • 3
1 Solution
 
HainKurtSr. System AnalystCommented:
try:

UPDATE tblPerson
SET closed = 1
WHERE createDate < convert(datetime, '07/19/2010', 101)

for more info convert string <--> date check here:

http://www.sqlusa.com/bestpractices/datetimeconversion/
0
 
lxbostonAuthor Commented:
Hi HainKurt

Thanks for your reply.

I tried the code you suggested  

UPDATE tblPerson
SET closed = 1
WHERE createDate < convert(datetime, '19/07/2010', 101)

Open in new window


(note I changed the date order (this is how we have it for Australia))

Got the following error

 
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

Open in new window

0
 
HainKurtSr. System AnalystCommented:
dont change the order :)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
HainKurtSr. System AnalystCommented:
use 103 instead

UPDATE tblPerson
SET closed = 1
WHERE createDate < convert(datetime, '19/07/2010', 103)
0
 
lxbostonAuthor Commented:
Hi HainKurt

That worked Thank you!

One more question, if I was wanting to delete rows based on the createDate rule what would I use?
0
 
HainKurtSr. System AnalystCommented:
try:

delete * from tblPerson
WHERE createDate < convert(datetime, '19/07/2010', 103)

or

delete from tblPerson
WHERE createDate < convert(datetime, '19/07/2010', 103)

0
 
lxbostonAuthor Commented:
Thanks HainKurt been a great help!

Got one more housekeeping tasks, we have a list of users based on their email which are hard bounces and we wish to tick to closed also.

I have the list in an xls spreadsheet.

Question:

How do I do a similar 'closed' '1' statement for entries that match entries from "email" column in the table.

Do I import the entries into a new table?
0
 
HainKurtSr. System AnalystCommented:
easy way is load them into a new table

emails(email varchar)

then run this:

delete from tblPerson
WHERE email in (select email from emails)
0
 
QlemoC++ DeveloperCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now