Solved

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

Posted on 2011-03-23
10
373 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
 
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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 68

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now