Daily stored proc with today's date in where

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

SQL Server Development (2000)

Daily stored proc with today's date in where

Author

Topic

Huligan
Yak Posting Veteran

66 Posts

Posted - 2007-02-22 : 09:35:46

Hello. I'm sure I'm missing something easy here, but I can't find the syntax. I have a stored procedures that runs some calculations on lots of records every night. I need to change it so that it only grabs the records from the previous day. So my where statement should be:

Where Date = 2/21/2007

OR

Where Date > 2/21/2007 12:00:01 AM And Date < 2/21/2007 11:59:59 PM

What is the syntax for calling today's date inside of a stored procedure? Thanks.

Les

Kristen
Test

22859 Posts

Posted - 2007-02-22 : 09:41:24

[code]
WHERE MyDateColumn >= DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 0)
AND MyDateColumn < DATEADD(Day, DATEDIFF(Day, 0, GetDate())+1, 0)
[/code]
If it is already "tomorrow" when you do this, and it should process "yesterdays" data, then you'll need to offset these by -1

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-22 : 09:43:27

A better way to do is would be:
Where Date >= Today_at_00:00:00 And Date < tomorrow_at_00:00:00

This code should do it:

where
	-- Date GE Today
	Date >= dateadd(dd,datediff(dd,0,getdate()),0) and
	-- Date Lt Tomorrow
	Date < dateadd(dd,datediff(dd,0,getdate())+1,0)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources