SQL Update/WHERE Statement is Today

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Update/WHERE Statement is Today

Author

Topic

frogoth
Starting Member

2 Posts

Posted - 2005-08-02 : 13:30:39

I am working with an SQL Server 2000, and am having difficulty getting the WHERE statement to look for today's date.

UPDATE myLog
SET status = 'Expired'
WHERE expires = ___________
AND status = 'Valid';

The expires field is a datetime field.

The terms Now() and getDate() do not work.

It works if I provide the variable, '#expires#'. However, that means I must run it manually everyday.
I want it to automatically update the status field every 24 hours WHERE expires = today's date.

Thanks for any forthcoming assistance!

Kristen
Test

22859 Posts

Posted - 2005-08-02 : 13:53:31

Is this SQL or Access?

If SQL then GetDate() should work (provided that "expires" is of datetime datatype).

If SQL then you can schedule a task to run the command at, say, Midnight every night.

Just to be clear: You want to update ALL rows in "myLog" so that their "expires" date/time is set to the current date/time?

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-02 : 14:00:40

I think the key here is WHERE expires = today's date and not today's date AND time. Right?

Tara

Kristen
Test

22859 Posts

Posted - 2005-08-02 : 14:49:02

Oh blimey, you're kidding me Tara. The expires was in the WHERE? Yah ... you're right<sigh>

OK, start again :-(

Sorry frogoth.

So, you want to select everything "today" and update its status.

SQL Server has a datetime datatype. This is a pain for this, because it inherently includes TIME (unless you explicitly create "expires" columns with dates at "midnight")

So you probably need to do something like:

UPDATE myLog
SET status = 'Expired'
WHERE (expires >= '02-Aug-2005' AND expires < '03-Aug-2005')
AND status = 'Valid'

And to do that dynamically, using "Todays date" is a proper piece of tautology:


UPDATE myLog
SET status = 'Expired'
WHERE     expires >= DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 0)
      AND expires < DATEADD(Day, DATEDIFF(Day, 0, GetDate())+1, 0)
      AND status = 'Valid'

Kristen

frogoth
Starting Member

2 Posts

Posted - 2005-08-02 : 19:44:01

Thanks so much to both of you.
It feels sooooo good to stop beating my head against the wall.

Kristen, the code you supplied worked beautifully.
I have scheduled the file to run on the server tomorrow at 6:00am.
I anticipate that all will go well.

Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources