Remove rows from the past (DATEDIFF question)

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)

Remove rows from the past (DATEDIFF question)

Author

Topic

Linok
Starting Member

34 Posts

Posted - 2006-07-12 : 14:02:10

Hey everyone,

I'm working on this stored procedure that returns all the rows for an events calendar based on the date. However, now I need to modify it to not return rows that are a day before a certain row occurs. I'm really new to T-SQL and know that it'll require the DATEDIFF, but I'm not sure how to set it up.

Here's the current SP:

ALTER PROCEDURE coglej.sp_GetEventsForDate
@eventdate datetime,
@eventowner varchar (50)

AS 

IF DATEDIFF(ww,GetDate(),@eventdate ) > 2 
BEGIN
PRINT 'Getting early bird event price.'
SELECT     dbo.events.event_date, dbo.locations.city, dbo.locations.state, dbo.locations.name, dbo.locations.address1, dbo.locations.address2, dbo.locations.zip, dbo.locations.description,
                      dbo.skus.sku_id, dbo.skus.event_type, dbo.skus.price, dbo.skus.slots_available
FROM         dbo.events INNER JOIN
                      dbo.locations ON dbo.events.location_id = dbo.locations.location_id INNER JOIN
                      dbo.skus ON dbo.events.sku_id = dbo.skus.sku_id
WHERE     (dbo.events.event_date = CONVERT(datetime, @eventdate)) AND (dbo.skus.owner = @eventowner) AND (dbo.skus.price_type = 'Early')

END
ELSE
BEGIN
PRINT 'Getting regular event price.'
SELECT     dbo.events.event_date, dbo.locations.city, dbo.locations.state, dbo.locations.name, dbo.locations.address1, dbo.locations.address2, dbo.locations.zip, dbo.locations.description,
                      dbo.skus.sku_id, dbo.skus.event_type, dbo.skus.price, dbo.skus.slots_available
FROM         dbo.events INNER JOIN
                      dbo.locations ON dbo.events.location_id = dbo.locations.location_id INNER JOIN
                      dbo.skus ON dbo.events.sku_id = dbo.skus.sku_id
WHERE     (dbo.events.event_date = CONVERT(datetime, @eventdate)) AND (dbo.skus.owner = '@eventowner') AND (dbo.skus.price_type = 'Regular')

END

Does anyone have any ideas?

I've tried adding " AND (DATEDIFF(dd, @eventdate, GetDate()) < 1)" to the end of the WHERE statements by no luck.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-12 : 14:55:33

[code]ALTER PROCEDURE coglej.sp_GetEventsForDate
(
@eventdate datetime,
@eventowner varchar (50)
)
AS

IF DATEDIFF(ww, GetDate(), @eventdate ) > 2
BEGIN
PRINT 'Getting early bird event price.'
SELECT dbo.events.event_date, dbo.locations.city, dbo.locations.state, dbo.locations.name, dbo.locations.address1, dbo.locations.address2, dbo.locations.zip, dbo.locations.description,
dbo.skus.sku_id, dbo.skus.event_type, dbo.skus.price, dbo.skus.slots_available
FROM dbo.events INNER JOIN
dbo.locations ON dbo.events.location_id = dbo.locations.location_id INNER JOIN
dbo.skus ON dbo.events.sku_id = dbo.skus.sku_id
WHERE (dbo.events.event_date >= DATEADD(ww, 2, getdate())) AND (dbo.skus.owner = @eventowner) AND (dbo.skus.price_type = 'Early')

END
ELSE
BEGIN
PRINT 'Getting regular event price.'
SELECT dbo.events.event_date, dbo.locations.city, dbo.locations.state, dbo.locations.name, dbo.locations.address1, dbo.locations.address2, dbo.locations.zip, dbo.locations.description,
dbo.skus.sku_id, dbo.skus.event_type, dbo.skus.price, dbo.skus.slots_available
FROM dbo.events INNER JOIN
dbo.locations ON dbo.events.location_id = dbo.locations.location_id INNER JOIN
dbo.skus ON dbo.events.sku_id = dbo.skus.sku_id
WHERE (dbo.events.event_date < DATEADD(ww, 2, getdate())) AND (dbo.skus.owner = '@eventowner') AND (dbo.skus.price_type = 'Regular')

END[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources