SELECT records with a date with the past [value]

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 2005 Forums

Transact-SQL (2005)

SELECT records with a date with the past [value]

Author

Topic

eddy556
Starting Member

36 Posts

Posted - 2009-01-09 : 05:22:01

How do I create a stored procedure that accepts a date object (such as hour, day, week etc) and return values accordingly.

Here's what I've got so far:

CREATE PROCEDURE [dbo].[CHSP_OR_MATERIALHAZARDUPDATE_001] @Interval VARCHAR(50)

AS

SELECT MTRLCODE, HAZARDCODE, LASTUPDATE
FROM Material_Hazard
WHERE (LASTUPDATE > DATEADD(day, - 1, GETDATE()))
ORDER BY MTRLCODE

I would @Interval to be able to change 'day' in the WHERE statement so it can be changed to hour, week, month etc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-09 : 05:31:00

[code]
CREATE PROCEDURE [dbo].[CHSP_OR_MATERIALHAZARDUPDATE_001] @Interval VARCHAR(50)

AS

SELECT MTRLCODE, HAZARDCODE, LASTUPDATE
FROM Material_Hazard
WHERE (LASTUPDATE > CASE @Interval
WHEN 'day' THEN DATEADD(dd, - 1, GETDATE())
WHEN 'hour' THEN DATEADD(hh, - 1, GETDATE())
WHEN 'minutes' THEN DATEADD(n, - 1, GETDATE()))
WHEN 'seconds' THEN DATEADD(ss, - 1, GETDATE())
END
)
ORDER BY MTRLCODE
[/code]

eddy556
Starting Member

36 Posts

Posted - 2009-01-09 : 05:32:04

I'm soo envious of your SQL abilities visakh16, thanks again :-)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-09 : 13:17:29

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources