2

I have several tables which typically have nullable "CreatedDate" and/or "ModifiedDate" datetime columns. I would like a query that can select from any single table where one, both, or neither datetime column exist and each column is BETWEEN provided (separate) ranges.

Here is a rough (non-working) example. This attempts to use COL_LENGTH to determine if a column exists. However, if a column is not present an error is thrown, I believe because the entire WHERE is still evaluated.

SELECT * FROM tableName
WHERE 1=1 AND
(COL_LENGTH(tableName, 'CreatedDate') IS NULL OR CreatedDate BETWEEN @CreatedFrom AND @CreatedTo) AND
(COL_LENGTH(tableName, 'ModifiedDate') IS NULL OR ModifiedDate BETWEEN @ModifiedFrom AND @ModifiedTo)

This is solvable with dynamic SQL. However, in this case I am restricted to read-focussed permissions. So no EXEC, CREATE, or INSERT.

Is this possible with static SQL?

Example tables

With both columns:

DECLARE @Table1 table
(
    Id int NOT NULL,
    CreatedDate datetime NULL,
    ModifiedDate datetime NULL
);

INSERT @Table1
    (Id, CreatedDate, ModifiedDate)
VALUES
    (1, NULL, NULL),
    (2, NULL, '20230101 01:23:45.678'),
    (3, '20230101 02:34:56.789', NULL),
    (4, '20230101 03:45:00.891', '20230101 04:56:00.789');

DECLARE 
    @CreatedFrom datetime = {TS '2023-01-01 01:30:00.000'},
    @CreatedTo datetime = {TS '2023-12-31 23:30:00.000'},
    @ModifiedFrom datetime = {TS '2023-01-01 06:00:00.000'},
    @ModifiedTo datetime = {TS '2023-12-31 18:00:00.000'};

With one column:

DECLARE @Table2 table
(
    Id int NOT NULL,
    CreatedDate datetime NULL
);

INSERT @Table2
    (Id, CreatedDate)
VALUES
    (1, NULL),
    (2, '20230101 02:34:56.789');

DECLARE 
    @CreatedFrom datetime = {TS '2023-01-01 01:30:00.000'},
    @CreatedTo datetime = {TS '2023-12-31 23:30:00.000'};

Thank you @PaulWhite for the initial example.

2
  • Are the columns nullable, not nullable or vary (when they exist in the tables)? What do you want the query to return when none of the two column exists on the table? All rows or none? Oct 8 at 12:54
  • @ypercubeᵀᴹ Good point, I forget to mention nulls. In this case columns are nullable. For my purposes, any nulls can be replaced with a static date, eg. 2020-01-01 00:00:00. (Original edited to include the nullable mention).
    – camtech
    Oct 8 at 21:03

2 Answers 2

6

Yes, this tricky problem can be solved in plain SQL, with a rather obscure method.

For more detailed explanation and the original idea, see the answers from Andriy M and me in two similar questions:
Why can't I use a case statement to see if a column exists and not select from it?
How to select specific rows if a column exists or all rows if a column doesn't

In your case - and with the assumptions

  • the two columns (when they exist) are not nullable and
  • when none of the columns exists, we want to return all rows
  • when one of the two columns exists, we apply the criteria only for that column

This would work:

SELECT x.*
FROM 
    ( SELECT NULL AS CreatedDate,
             NULL AS ModifiedDate
    ) AS dummy
  CROSS APPLY
    ( SELECT t.*
      FROM dbo.tableName AS t
      WHERE ( CreatedDate BETWEEN @Date1 AND @Date2    -- the columns should
           OR CreatedDate IS NULL )                    -- be referenced
        AND ( ModifiedDate BETWEEN @Date3 AND @Date4   -- WITHOUT
           OR ModifiedDate IS NULL )                   -- a table prefix here
    ) AS x ;
1
  • 1
    This is great. I did see one of those previous questions but struggled with the syntax when adapting it to multiple columns and WHEREs. Of course it seems so simple once explained.
    – camtech
    Oct 8 at 23:09
4

It's possible to use XML for this:

DECLARE @Table table
(
    Col1 integer NOT NULL,
    CreatedDate datetime NULL
);

INSERT @Table
    (Col1, CreatedDate)
VALUES
    (1, '20230101 12:34:56.789'),
    (2, '20230101 01:23:45.678');

DECLARE 
    @CreatedFrom datetime = {TS '2023-01-01 01:30:00.000'},
    @CreatedTo datetime = {TS '2023-12-31 23:30:00.000'},
    @ModifiedFrom datetime = {TS '2023-01-01 06:00:00.000'},
    @ModifiedTo datetime = {TS '2023-12-31 18:00:00.000'};
SELECT 
    T.*
FROM @Table AS T
CROSS APPLY (SELECT T.* FOR XML PATH, TYPE) AS A (x)
WHERE 
    1 = 1
    AND 1 = A.x.exist('
        row
        [
            not(CreatedDate) (: column does not exist :)
            or 
            (
                CreatedDate >= sql:variable("@CreatedFrom")
                and CreatedDate <= sql:variable("@CreatedTo")
            )
        ]'
    )
    AND 1 = A.x.exist('
        row
        [
            not(ModifiedDate) (: column does not exist :)
            or 
            (
                ModifiedDate >= sql:variable("@ModifiedFrom")
                and ModifiedDate <= sql:variable("@ModifiedTo")
            )
        ]'
    );

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.