job to set status to inactive after 24 hours 1

[TMac42]

I've got a table that gets fed data throughout the day. When a new row is inserted, I insert the values into the table, including a column of "DateTimeCreated" with Now(). This column is a datetime.

But, if they later update the row, they send me a string value of date and time to use in a "DateTimeUpdated" column with a string datatype. And I have to use that value instead of Now().

This is not a huge deal because until recently we only stored and displayed this data. Now we need to update another column in that row - status - and set it to inactive (0) for any record that was created more than 24 hours prior. If the row was updated, we use that value instead and it's the text value.

Here's what I'm using so far and it's bombing out because of the different datatypes:

update TableName SET Status = 0
WHERE CONVERT(DateTime,CONVERT(varchar(100),ISNULL(DateTimeUpdated, DateTimeCreated))) < DateAdd(d,-1,GetDate())

We ran this the other day and it worked...I swear it did. But it doesn't work now. Anyone have any suggestions? Thanks.

 

[gmmastros]

You may have bad data in the DateTimeUpdated field. Try running this sql string.

Select DateTimeUpdated 
From TableName 
Where IsDate(DateTimeUpdated) = 0

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[TMac42]

I get the following error:

"Argument data type text is invalid for argument 1 of isdate function

 

[gmmastros]

You should be using the varchar data type for this field. The Text data type can store 2 gigabytes of text, but you are limited in what you can do with it.

Try the following sql string to check for bad data. The results may not be exactly what you expect since you are using a text field.

Select DateTimeUpdated 
From TableName 
Where IsDate(SubString(DateTimeUpdated, 1, 10)) = 0

Just out of curiosity, run this sql string.

Select Max(DataLength(DateTimeUpdated))
From TableName

This sql string will show you the maximum length of data in that field.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[TMac42]

The first SELECT returns <NULL>.

The second SELECT returns 20.

 

[vongrunt]

This code is a little bit more clear

UPDATE TableName 
SET Status = 0
WHERE ISNULL(CONVERT(datetime, DateTimeUpdated [ ,specify format here if necessary] ), DateTimeCreated) < GetDate() - 1

... but varchardates are evil and the only 100% safe way is to change DateTimeUpdated to datetime.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[TMac42]

Thanks, vongrunt... that did the trick once I changed my datatype on that column to a varchar(75).

 

[TMac42]

Okay, based on the above...this is what I have and I'm getting "Syntax error converting datetime from character string".

UPDATE Alerts
SET Status = 0
WHERE ISNULL(CONVERT(datetime, DateTimeUpdated), DateTimeCreated) < GetDate() - 1

Keep in mind that in my database, DateTimeCreated is a datetime while DateTimeUpdated is now a varchar 75. The incoming data I receive is in this format, so that's why I have to work with it like this.

How can I get around the above error? Thanks.

 

[vongrunt]

<preaching>
Because varchardates are evil. Nuff said
</preaching>

Perhaps incoming data is in another format - mdy instead of dmy or whatever. In that case specify 3rd CONVERT() parameter (style) as previously suggested.

If not, junk in data is another possibility. This should isolate problematic "dates":

select DateTimeUpdated
from Alerts
where DateTimeUpdated is not null and DateTimeUpdated <> '' 
	and isdate(DateTimeUpdated) = 0

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)