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
lxbostonAsked:
Who is Participating?
 
HainKurtSr. System AnalystCommented:
use 103 instead

UPDATE tblPerson
SET closed = 1
WHERE createDate < convert(datetime, '19/07/2010', 103)
0
 
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
HainKurtSr. System AnalystCommented:
dont change the order :)
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.