change status if record created > 24 hours

[TMac42]

How can I simply update a status column to "inactive" for any record that was created more than 24 hours ago? I have a column already for "DateTimeCreated" but it is stored as a string (don't ask). Thoughts?

Thanks.

 

[amrita418]

TMac42,

create table #test (status int, DateTimeCreated varchar(19))

insert into #test values(1, '04-21-2005 12:00:00')
insert into #test values(, '05-20-2005 04:00:00')

update #test
set status = 2
where datediff(hh, DateTimeCreated, getdate()) >= 24

Regards,
--aa

 

[vongrunt]

If DateTimeCreated "date" is in correctly recognizable format, then:

update mytable
set status = somevalue
where DateTimeCreated < getdate() - 1

------
"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)

 

[Catadmin]

The simplest way to do this is to create another column that has a default of GetDate() for every entry, then do your formula on that.

Or you could do as Vongrunt suggested, but I believe you'll have to do a Convert or Cast of the varchar to datetime datatype... Assuming, as he said, that it is in the correct format to begin with.

Where Cast(DateTimeCreated as datetime) < (GetDate() - 1)



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???