Solved

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

Posted on 2011-03-23
10
376 Views
Last Modified: 2012-05-11
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
Comment
Question by:lxboston
  • 5
  • 3
10 Comments
 
LVL 51

Expert Comment

by:HainKurt
ID: 35204145
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
 

Author Comment

by:lxboston
ID: 35204224
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
 
LVL 51

Expert Comment

by:HainKurt
ID: 35204236
dont change the order :)
0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
LVL 51

Accepted Solution

by:
HainKurt earned 500 total points
ID: 35204238
use 103 instead

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

Author Comment

by:lxboston
ID: 35204280
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
 
LVL 51

Expert Comment

by:HainKurt
ID: 35204337
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
 

Author Comment

by:lxboston
ID: 35204568
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
 
LVL 51

Expert Comment

by:HainKurt
ID: 35206771
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
 
LVL 69

Expert Comment

by:Qlemo
ID: 36032418
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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql help 2 45
Problem when I run a simple storeproc - help 4 23
Format Data Field - SQL 11 37
SQL Server - Getting the most recent engagement for each contact 9 36
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question