SQLTeam.com | Weblogs | Forums

SQL Case statement in Where condition with Datetime

#1

Hi Friends,

I am new to sql help me.

I wants to take CID,CName from Table1

Table1

CID CName ActiveDateTime
1 A 2015-06-16 23:33:16.653
2 B 2015-07-16 22:33:16.653
3 C 2015-07-02 12:00:16.653
4 D 2015-07-10 01:33:16.653
5 E 2015-07-01 01:33:16.653

Current DateTime - 2015-07-01 05:30:16.653

When @Status = -1

Display ALL records

Result

1 A
2 B
3 C
4 D
5 E

When @Status = 1

Display Active records only i.e., DateTime greater than current datetime

2 B
3 C
4 D

When @Status = 0

Display InActive records only i.e., DateTime lesser than current datetime

1 A
5 E

#2 
DECLARE @status TINYINT = 1;

SELECT
	CID, CNAME
FROM
	Table1
WHERE
	@status = -1
	OR
	(@status = 1 AND ActiveDateTime > GETDATE())
	OR
	(@status = 0 AND ActiveDateTime < GETDATE());
#3

Hi JamesK,

Thank you for your reply,

@Status was not the column of table1.
@Status was user input

When @Status = -1

Result

1 A
2 B
3 C
4 D
5 E

When @Status = 1

Display Active records only i.e., DateTime greater than current datetime

2 B
3 C
4 D

When @Status = 0

Display InActive records only i.e., DateTime lesser than current datetime

1 A
5 E

#4

That is ok - the query I posted earlier is meant exactly for that situation. @Status is a local variable, not a column in the table. You would set the user input to the value of that variable and run the query.

You could even make a stored procedure with @status as a parameter and then run it using appropriate value for the parameters. If you want to do that, it would be like this:

CREATE PROCEDURE dbo.GetTable1Records
	@Status TINYINT
AS
	SET NOCOUNT,XACT_ABORT ON;
	
	SELECT
		CID, CNAME
	FROM
		Table1
	WHERE
		@status = -1
		OR
		(@status = 1 AND ActiveDateTime > GETDATE())
		OR
		(@status = 0 AND ActiveDateTime < GETDATE());
GO

Running the above code will create the stored proc in your database.
Then, you can use it like shown below:

EXEC dbo.GetTable1Records @status = 1;

-- or
EXEC dbo.GetTable1Records @status = 0;

-- or
EXEC dbo.GetTable1Records @status = -1;
#5

Thank you JamesK