How to convert datetime into string and declare as parameter in sql server?

Question

i have date time field in my table. i want to convert into string and declare as parameter.

For example when datetime field is not null then 'OPEN' and datetime field is null then 'CLOSED' and when i enter the parameter, i want type 'OPEN' or 'CLOSED' then the result will display the text in the table.

How to do that?.

Answer 1

I don't quite follow your question about using the 'parameter' but hopefully this will give you most of what you need.

SELECT CASE 
           WHEN [Date_Column] IS NULL THEN 'CLOSED'
           ELSE 'OPEN'
       END
FROM [Your_Table]

Answer 2

This is how I understand your question: you want a query with a string parameter accepting two fixed values, either 'OPEN' or 'CLOSED', and you want one to match non-NULL values of a certain datetime column in your table, and the other to match NULL values of the same column. And you also want to display values of that column as 'OPEN' or 'CLOSED' accordingly.

If that is correct, you could try the following:

DECLARE @QueryParam varchar(20);
SET @QueryParam = 'OPEN';

SELECT *
FROM (
  SELECT
    OpenOrClosed = CASE WHEN DateTimeColumn IS NULL THEN 'CLOSED' ELSE 'OPEN' END,
    other columns as necessary
  FROM yourtable
) s
WHERE OpenOrClosed = @QueryParam;

You might also want to consider rewriting the subselect as a view:

CREATE VIEW YourTableView
AS
SELECT
  OpenOrClosed = CASE WHEN DateTimeColumn IS NULL THEN 'CLOSED' ELSE 'OPEN' END,
  other columns as necessary
FROM yourtable

Then you would just select from that view:

DECLARE @QueryParam varchar(20);
SET @QueryParam = 'OPEN';

SELECT *
FROM YourTableView
WHERE OpenOrClosed = @QueryParam;