Solved

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

Posted on 2011-03-23
10
378 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
10 Comments
 
LVL 54

Expert Comment

by:Huseyin KAHRAMAN
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 54

Expert Comment

by:Huseyin KAHRAMAN
ID: 35204236
dont change the order :)
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 54

Accepted Solution

by:
Huseyin KAHRAMAN 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 54

Expert Comment

by:Huseyin KAHRAMAN
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 54

Expert Comment

by:Huseyin KAHRAMAN
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 70

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

734 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