locked
Compare Date in T-SQL RRS feed

  • Question

  • I have 2 columns as the following:
    Activation(Date)           Subscription (int - measured in month)

    2009-06-20                          6
    2008-02-14                          12

    How do I check if the subscription hasn’t expired using T-SQL
    Activation Date + Month >= GetDate() is the formula.
    Returned value should be true or false only

    CREATE TABLE Subscription(
    	[SubscriberID] [int] IDENTITY(1,1) NOT NULL,
    	[Activation] [date] NULL,
    	[Subscription] [int] NULL)
    
    CREATE  PROCEDURE [dbo].[sp_Check]
    	@SubscriberID int
    AS
    BEGIN 
                  --T-SQL here 
    END
    • Edited by CodesCrawler Thursday, September 17, 2009 3:45 PM
    Thursday, September 17, 2009 3:32 PM

Answers

  • Is this what you need?




    Create  PROCEDURE [dbo].[sp_Check] @SubscriberID int 
    AS
    BEGIN 
    SET NOCOUNT ON 
    DECLARE @Check int
    SELECT @Check = 0
     
    SELECT @Check = SubscriberID
    FROM Subscription
    WHERE DATEADD(mm,Subscription,Activation) < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
    and SubscriberID = @SubscriberID
    
    IF @Check = 0 OR @Check IS NULL
    	BEGIN
    		RETURN 0	END
    ELSE
    	BEGIN
    		RETURN 1
    	END
    
    END
    


    AE, PMP, MCTS
    • Edited by Abdshall Thursday, September 17, 2009 3:57 PM Format
    • Marked as answer by CodesCrawler Thursday, September 17, 2009 4:03 PM
    Thursday, September 17, 2009 3:56 PM

All replies


  • SELECT SubscriberID
    FROM Subscription
    WHERE DATEADD(mm,Subscription,Activation) < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
    Thursday, September 17, 2009 3:39 PM
  • try this one      

    select SubscriberID from Subscription where Convert(int,MONTH('Activation'))+ Subscription < Convert(int,MONTH(GETDATE()))
    • Proposed as answer by kchokkal Thursday, September 17, 2009 4:16 PM
    Thursday, September 17, 2009 3:45 PM
  • Yes, you are right. I overlooked your requirements. Sorry about that

    SELECT SubscriberID
    FROM Subscription
    WHERE DATEADD(mm,Subscription,Activation) < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

    AE, PMP, MCTS
    Thursday, September 17, 2009 3:46 PM
  • What is your InputID?
    AE, PMP, MCTS
    Thursday, September 17, 2009 3:48 PM
  • Is this what you need?




    Create  PROCEDURE [dbo].[sp_Check] @SubscriberID int 
    AS
    BEGIN 
    SET NOCOUNT ON 
    DECLARE @Check int
    SELECT @Check = 0
     
    SELECT @Check = SubscriberID
    FROM Subscription
    WHERE DATEADD(mm,Subscription,Activation) < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
    and SubscriberID = @SubscriberID
    
    IF @Check = 0 OR @Check IS NULL
    	BEGIN
    		RETURN 0	END
    ELSE
    	BEGIN
    		RETURN 1
    	END
    
    END
    


    AE, PMP, MCTS
    • Edited by Abdshall Thursday, September 17, 2009 3:57 PM Format
    • Marked as answer by CodesCrawler Thursday, September 17, 2009 4:03 PM
    Thursday, September 17, 2009 3:56 PM