Member Avatar for zenocide

Why is this script not working..

USE [KN_online]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[UPDATE_PREMIUM_SERVICE_USER_EXEC] 

AS 
 
DECLARE @d24 datetime

SET @d24 = DateAdd(hh,-24, GETDATE())
UPDATE PREMIUM_SERVICE SET nDays = nDays - 1 where Date > @d24

This is suppose to take 1 day of a users remaming days every 24 hours,, But its not working..

Please help.

  • 1 Contributor
  • 2 Replies
  • 95 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by zenocide
Member Avatar for zenocide

Ok got the first part done.

Just another little problem..

USE [KN_online]
GO
/****** Object:  StoredProcedure [dbo].[UPDATE_PREMIUM_SERVICE_USER_EXEC]    Script Date: 07/20/2008 17:28:50 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[UPDATE_PREMIUM_SERVICE_USER_EXEC] 

AS 
 
DECLARE @d24 smalldatetime

--- Below \/ = Check if date has been more than 24 hours,
--- If yes then deduct 1 day for Premium 
---             WORKS 100%

SET @d24 = DateAdd(hh,-24, GETDATE())

UPDATE PREMIUM_SERVICE_EXEC SET nDays = nDays - 1 where @d24 > Date


--- Below \/ = Check if Date has been more than 24 Hours, If yes start Begin
---              DONT WORK ATALL

SET @d24 = DateAdd(hh,-24, GETDATE())

if @d24 = getdate()
             
BEGIN

UPDATE PREMIUM_SERVICE_EXEC SET Date = getdate()

END
Member Avatar for zenocide

I got this, but its updating the info every 15 mins, I need it to be every 1440 mins or 24 hours.

Can anyone help me?

DECLARE @d24 smalldatetime


SET @d24 = CONVERT(VARCHAR(15) , GETDATE(),102)
SET @d24 = DATEADD(HH,DATEPART(HH,dateadd(MI, 10, GETDATE())),@d24)

UPDATE PREMIUM_SERVICE SET nDays = nDays - 1 where @d24 > Date
UPDATE PREMIUM_SERVICE SET Date = getdate()where @d24 > Date
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.