Working with datetime with dynamic query in SQL Server

Question

I am using a dynamic query wherein I want to use the variable which holds the datetime, whenever I execute the query it says cannot convert datetime from string, when I cast that variable to varchar(max), it takes it as string and not datetime, so how should I execute the query..

Below is my SQL query which I am trying to execute.

SET @SQL1 = 'SELECT B.FacId, B.FacName, B.BookCode, B.BookName, B.Quantity, 
CONVERT(VARCHAR(10), B.TillDate, 104) AS TILLDATE FROM '+@TABLE+' B
WHERE B.TillDate BETWEEN CONVERT(VARCHAR(10),'+@FROMDATE+', 101) and 
CONVERT(VARCHAR(10), DATEADD(DD,1,'+@TODATE+'), 101)'

EXEC SP_EXECUTESQL @SQL1

here @fromdate and @todate are the datetime type coming from different temp table. and stored in these variable..

How should I execute this query?

Bobby · Accepted Answer · 2015-01-08 16:16:17Z

38

You need to quote your dates..

SET @SQL1 = 
   'SELECT B.FacId, 
           B.FacName, 
           B.BookCode, 
           B.BookName, 
           B.Quantity, 
           CONVERT(VARCHAR(10), B.TillDate, 104) AS TILLDATE 
           FROM '+@TABLE+' B 
           WHERE B.TillDate BETWEEN ''' + CONVERT(VARCHAR(10),@FROMDATE, 101) + ''' and ''' + CONVERT(VARCHAR(10),DATEADD(DD,1,@TODATE), 101) + ''''

edited Jan 8, 2015 at 16:16

Bobby

2,9383 gold badges22 silver badges37 bronze badges

answered Feb 17, 2011 at 11:59

bleeeah

3,60422 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ColinMac Over a year ago

This is the right answer. I had the exact same issue and this solved it.

Filip De Vos · Accepted Answer · 2018-03-06 15:39:53Z

9

You should not concatenate your parameter values like this. The best solution is to use a parameterized query with sp_executesql.

DECLARE @sql nvarchar(4000)

select @sql = N'
  SELECT B.FacId 
       , B.FacName
       , B.BookCode
       , B.BookName
       , B.Quantity
       , CONVERT(VARCHAR(10), B.TillDate, 104) AS TILLDATE 
    FROM ' + quotename(@TABLE) + N' B
   WHERE B.TillDate BETWEEN cast(floor(cast(@fromDate as float)) as datetime)
                        AND cast(floor(cast(@toDate as float)) as datetime)'

EXEC sp_executesql @sql, N'@fromDate datetime, @toDate datetime', @FROMDATE, @TODATE

Things to note about sp_executesql are:

The parameters are NVARCHAR values
The 3rd and 4th parameter keep their original datatype and do not need to be converted to a varchar. This again protects agains SQL Injection and it makes the query more readable as you prevent the quote soup which is so common in Dynamic SQL

Some additional changes were applied to the query:

The table name is wrapped in the QUOTENAME() function which protects against sql injection on the object name
The way the date part of the datetime variables is removed is not very optimal. Doing a convert(,,101) is an expensive operation which can better be done using the casting to float and taking floor of that value.

edited Mar 6, 2018 at 15:39

answered Feb 17, 2011 at 21:25

Filip De Vos

12k1 gold badge50 silver badges62 bronze badges

4 Comments

Joel Mansford Over a year ago

What's the point in taking @fromDate which is a datetime then casting it to a float just to cast it back to a datetime again?

Filip De Vos Over a year ago

@joel This chops off the timestamp part of the datetime without converting to a string. (Dates are numeric internally)

NKAT Over a year ago

Would you please specify on why is it the best approach. Is it because 'cast(float(cast' tastes better that some soup + CONVERT(VARCHAR(10),@FROMDATE, 101) or anything else?

Filip De Vos Over a year ago

I haven't checked performance of the convert versus cast to float in the last 10 years, but it used to be slower.

Joel Mansford · Accepted Answer · 2011-02-17 22:33:12Z

I'd like to see your variable definitions but I suspect it's because @FROMDATE and @TODATE are datetime and you're using them in a string concatenation statement. Thus you can fix it by:

SET @SQL1 = 'SELECT B.FacId, B.FacName, B.BookCode, B.BookName, B.Quantity, CONVERT(VARCHAR(10), B.TillDate, 104) AS TILLDATE FROM '+@TABLE+' B WHERE B.TillDate BETWEEN CONVERT(VARCHAR(10),'+CAST(@FROMDATE as varchar(15))+', 101) and CONVERT(VARCHAR(10), DATEADD(DD,1,'+CAST(@TODATE as varchar(15))+'), 101)'

However better solutions are:

Don't use dynamic SQL at all, maybe @TABLE doesn't vary that much and you can union them in to a view or something
Pass the parameters directly in to sp_executeSQL and thus preserve their types e.g.

SET @SQL1 = 'SELECT B.FacId, B.FacName, B.BookCode, B.BookName, B.Quantity, B.TillDate AS TILLDATE FROM '+@TABLE+' B WHERE B.TillDate BETWEEN @inFROMDATE and @inTODATE'

EXEC SP_EXECUTESQL @SQL1,'@inFROMDATE datetime, @inTODATE',@inFromDate = @FROMDATE, @inTODATE = @TODate

Bobby · Accepted Answer · 2015-01-08 15:43:40Z

2

I think this might work:

 DECLARE @tempdate datetime
 SET tempdate =DATEADD(DD,1,@TODATE)
 SET @SQL1 = 'SELECT B.FacId, B.FacName, B.BookCode, B.BookName, B.Quantity,'''+  cast     (B.TillDate as VARCHAR(50))+''' AS TILLDATE FROM '+@TABLE+' B WHERE B.TillDate BETWEEN '''+cast(@FROMDATE as VARCHAR(50))+''' and  '''+cast(@tempdate as VARCHAR(50))'''

 EXEC SP_EXECUTESQL @SQL1

edited Jan 8, 2015 at 15:43

Bobby

2,9383 gold badges22 silver badges37 bronze badges

answered Feb 17, 2011 at 13:36

MaryamAyd

1511 silver badge9 bronze badges

Comments

bernd_k · Accepted Answer · 2011-02-17 12:24:03Z

0

Try this:

declare @sql1 varchar(max)
declare @table sysname
declare @FROMDATE datetime
declare @TODATE datetime

set @table = 'MyTable'
set @FROMDATE = GETDATE()
set @ToDATE = GETDATE()


SET @SQL1 = 'SELECT B.FacId, B.FacName, B.BookCode, B.BookName, B.Quantity, 
CONVERT(VARCHAR(10), B.TillDate, 104) AS TILLDATE FROM '+@TABLE+' B
WHERE B.TillDate BETWEEN CONVERT(Datetime,''' + CONVERT(VARCHAR(10),@FROMDATE, 101) 
+ ''', 101) and CONVERT(DATETIME,'''+ CONVERT(VARCHAR(10), DATEADD(DD,1,@TODATE), 101) + ''', 101)'


print @sql1

But look at Joel Mansford's answer to avoid the double conversion.

edited Feb 17, 2011 at 12:24

answered Feb 17, 2011 at 12:06

bernd_k

12.1k7 gold badges50 silver badges64 bronze badges

Comments

Hardik · Accepted Answer · 2013-05-08 06:13:20Z

0

This is late, but may be it help someone

What you need is a quotes around your date, You already got your answer.

Below is an example of what I usually put in my query

'(CONVERT(DATETIME,CONVERT(varchar,gd.CreatedDate),106) <= CONVERT(DATETIME,'''+CONVERT(varchar, @EndDate ) +''',106))'

note that @EndDate is of type Datetime here

answered May 8, 2013 at 6:13

Hardik

1,7646 gold badges27 silver badges41 bronze badges

Comments

Seamus · Accepted Answer · 2018-09-26 22:35:30Z

0

Run this example and adapt it to your code. (There are not 3 contiguous single quotes)

Declare @FromDATE  datetime
;Declare @ToDATE datetime
;set @FromDATE = getdate() 
;set @ToDATE = @FromDATE 

;Print 'WHERE TillDate BETWEEN ' + char(39) + CONVERT(VARCHAR(10),@FromDATE, 101) 
+ char(39) + ' and ' + char(39) + CONVERT(VARCHAR(10),@ToDATE, 101) + char(39)

edited Sep 26, 2018 at 22:35

Seamus

4,8392 gold badges37 silver badges46 bronze badges

answered Sep 26, 2018 at 22:32

Gustavo

1

1 Comment

MUHAMMAD MUBUSHER ASLAM Over a year ago

instead of doing this you can add something like ''some text''

grantr · Accepted Answer · 2021-10-07 20:40:37Z

0

I needed to pass a date as a variable to a stored procedure I was executing within the dynamic SQL:

Declare @asOfDate date = '2020-01-01'
Declare @sql nvarchar(max)

Set @sql='SELECT * FROM OPENROWSET(''SQLNCLI''
        ,''Server=localhost;Database=YOURDB;Uid=DBO_DB;Pwd=password''
        ,''SET FMTONLY OFF;SET NOCOUNT ON;
                declare @date date ;
                select @date = CONVERT(DATE,CONVERT(varchar(10),'+ CONVERT(VARCHAR(100),@asOfDate , 112)+'));

                exec your_proc_here @date

                '')'

 Print @sql
 --Exec(@sql)

answered Oct 7, 2021 at 20:40

grantr

1,07614 silver badges17 bronze badges

Collectives™ on Stack Overflow

Working with datetime with dynamic query in SQL Server

8 Answers 8

1 Comment

4 Comments

Comments

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

1 Comment

4 Comments

Comments

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related