CODEWITHSUNDEEP
sqlt-sqldatetime
Mark
Mark

Reputation: 4883

TSQL - See if date falls between date

I have the following dynamic SQL query that I am using to filter results for a data range. If my "createdFrom" and "createdTo" date are not null, I add a where clause to my select statement.

I have 2 questions:

  1. How would you go about doing this? Is my logic correct?

  2. I am currently getting an error message when I add the below code, why would I be getting this?:

    Conversion failed when converting character string to smalldatetime data type.

Declare @createdFromDate SMALLDATETIME = NULL ,
        @createdToDate SMALLDATETIME = NULL 

 IF @createdFromDate IS NOT NULL 
    BEGIN
       SELECT @sWhere = @sWhere + 'AND g.CreatedOn > '+@createdFromDate
    END
 IF @createdToDate IS NOT NULL
    BEGIN
       SELECT @sWhere = @sWhere + ' AND g.CreatedOn <'+@createdToDate
    END

Upvotes: 0

Views: 125

Answers (3)

Stephan
Stephan

Reputation: 6018

What's causing your issue is your trying to concatenate a smalldatetime with, so string data-types like VARCHAR. You can just cast your dates as VARCHAR to get around that. CONCAT() would be even better though. It is your best friend when concatenating values of different data-types. I HIGHLY recommend using it, sadly it only is support by SQL Server 2012+ and above and not many know it even exists.

Now for you specific problem, I don't think dynamic SQL is necessary. You should generally try to avoid using dynamic SQL since it's hard to debug and make changes later. Something like this would work just fine for you.

Declare @createdFromDate SMALLDATETIME = NULL,
        @createdToDate SMALLDATETIME = NULL 

SELECT *
FROM yourTable
WHERE   (
            date_column > @createdFromDate
            OR @createdFromDate IS NULL
        )
        AND
        (
            date_column < @createdToDate
            OR @createdToDate IS NULL
        )

Upvotes: 2

ughai
ughai

Reputation: 9890

For your question

I am currently getting an error message when I add the below code, why would I be getting this?

The reason you are getting this error is when the query is executed your condition becomes

AND g.CreatedOn > 2015-05-07

Which is invalid, but instead your need

AND g.CreatedOn > '2015-05-07'

Hence your SQL should be 

SELECT @sWhere = @sWhere + 'AND g.CreatedOn > '''+@createdFromDate + ''''

For your question

How would you go about doing this? Is my logic correct?

You should use sp_executesql and pass the variable in your dynamic SQL like this.

IF @createdFromDate IS NOT NULL 
   BEGIN
      SELECT @sWhere = @sWhere + 'AND g.CreatedOn > @createdFromDate'
   END

IF @createdToDate IS NOT NULL
   BEGIN
      SELECT @sWhere = @sWhere + ' AND g.CreatedOn < @createdToDate'
   END

Instead of 

EXEC(@SQL)

You would use 

EXEC sp_executeSQL @SQL,N'@createdFromDate smalldatetime,@createdToDate smalldatetime',@createdFromDate,@createdToDate

Where @SQL is constructed from your @Where

Note: You don't need dynamic SQL if this is the only reason for using it.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271231

The direct answer to your question is that date constants need to be enclosed in single quotes. Instead of this:

SELECT @sWhere = @sWhere + 'AND g.CreatedOn > '+@createdFromDate

Use:

SELECT @sWhere = @sWhere + 'AND g.CreatedOn > ''' + @createdFromDate + ''''

The correct answer to your question is that you should use sp_executesql for executing dynamic SQL. This allows you to include parameters, so you can just say:

exec sp_executesql @sql,
    N'@createdFromDate date',
    @createdFromDate = @createdFromDate;

where @sql is a string that looks like:

select . . .
. . .
where . . . and
      g.CreatedOn = @createdFromDate;

Upvotes: 2

Related Questions