Dynamic sql syntax with dates

Question

This line in my dynamic sql which feeds some dynamic pivoting, does not seem to take in the correct dates and return the expected results. The query runs and returns no results.

and PA.DATE_RECEIVED BETWEEN 
'+ convert(varchar(10), @Startdate, 120) +' AND '+ convert(varchar(10), @Enddate, 120) +'

Check to see what values it is picking up ... Some sql in the stored procedure.

select convert(varchar(10), @Startdate, 120) - 2013-02-02    
select convert(varchar(10), @Enddate, 120) - 2013-02-26

Running the query outside in normal sql, it works ? What is the problem.

select COALESCE( PT.[description] , 'Grand Total') AS [Transaction Type], 
           Sum (AI.PRICE_INC_VAT) AS [AMOUNT (ú) CREDIT],
           P.[DESCRIPTION] AS [PRODUCT TYPE]    
From  [dbo].[T1] C     
join [dbo].[T2] S on S.[Customer_ID]=C.[Customer_ID]     
join [dbo].[T3] SO on SO.[SITE_ID]=S.[SITE_ID]    
join [dbo].[T4] OI on OI.[ORDER_ID]=SO.[SITE_ORDER_ID]    
left join [dbo].[T5] P on P.[PRODUCT_ID]=OI.[PRODUCT_ID]    
JOIN [dbo].[T6] AI ON  AI.ORDER_ITEM_ID = OI.ORDER_ITEM_ID    
JOIN T7 JBAI ON JBAI.ACTION_ITEM_ID = AI.ACTION_ITEM_ID    
JOIN T8 JB ON JB.JOB_BATCH_ID = JBAI.JOB_BATCH_ID    
JOIN T9 PA on PA.PAYMENT_ID=JB.PAYMENT_ID    
LEFT JOIN T10 CU ON JB.CUSTOMER_USER_ID = CU.CUSTOMER_USER_ID    
JOIN T11 PT ON PT.PAYMENT_TYPE_ID=PA.PAYMENT_TYPE_ID     
LEFT JOIN T12 SU ON SU.SYS_USER_ID=JB.SYS_USER_ID   
where P.[PRODUCT_CATEGORY_ID]= ( 
                        select PC.[PRODUCT_CATEGORY_ID] from [dbo].[PRODUCT_CATEGORY] PC 
                        where PC.[DESCRIPTION]='BAGS')     
and C.COMPANY_ID= '12'    
and PA.DATE_RECEIVED BETWEEN '02-FEB-2013' AND '26-FEB-2013'    
group by PT.DESCRIPTION, P.DESCRIPTION

Before you invoke EXEC() or sp_executesql on concatenated string that contains dynamic SQL call the PRINT @yourVariableName and see what it outputs, then try to run such query in SSMS and see where it differs from the query that is returning results. — Ivan Golović
– Ivan Golović, Commented Feb 26, 2013 at 9:52
PRINT Variable shows the line in dynamic sql as and PA.DATE_RECEIVED BETWEEN 2013-02-02 AND 2013-02-26 — James Khan
– James Khan, Commented Feb 26, 2013 at 10:02
What is the data type of PA.DATE_RECEIVED field? varchar or date/datetime? — Indi
– Indi, Commented Feb 26, 2013 at 10:06

Ivan Golović · Accepted Answer · 2013-02-26 10:11:57Z

3

You might be missing single quotes around dates, instead of PA.DATE_RECEIVED BETWEEN 2013-02-02 AND 2013-02-26 try to have the string read: PA.DATE_RECEIVED BETWEEN '2013-02-02' AND '2013-02-26'. Here is an example how you can get single quotes in string:

DECLARE @var VARCHAR(1000) = 
    'and PA.DATE_RECEIVED BETWEEN ''' + 
    convert(varchar(10), GETDATE(), 120) +
    ''' AND ''' + 
    convert(varchar(10), GETDATE(), 120) + ''''

SELECT @var

Single quote in a literal string is denoted by ''.

edited Feb 26, 2013 at 10:11

answered Feb 26, 2013 at 10:06

Ivan Golović

8,8223 gold badges27 silver badges31 bronze badges

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

3 Comments

James Khan Over a year ago

Closer. Get in prtin variable now . and PA.DATE_RECEIVED BETWEEN '2013-02-02' AND '2013-02-26' . But now get error sg 242, Level 16, State 3, Line 3 The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

James Khan Over a year ago

changed line to and PA.DATE_RECEIVED BETWEEN ''' + convert(varchar(10), Startdate, 120) +''' AND '''+ convert(varchar(10), Enddate, 120) +''' Ampersands not inlcuded in this comment

Ivan Golović Over a year ago

Try replacing format 120 with 112 (ISO format), like: convert(varchar(10), @Startdate, 112) and convert(varchar(10), @Enddate, 112)

mson · Accepted Answer · 2013-02-26 10:08:44Z

1

i think you got a quote problem. note the escaped quote to make a single quote appear in the dynamic query

and PA.DATE_RECEIVED BETWEEN '''+ convert(varchar(10), @Startdate, 120) +''' AND '''+ convert(varchar(10), @Enddate, 120) +'''

answered Feb 26, 2013 at 10:08

mson

7,8426 gold badges43 silver badges70 bronze badges

Comments

jazzytomato · Accepted Answer · 2013-02-26 10:04:20Z

0

Have you tried this :

and PA.DATE_RECEIVED BETWEEN @Startdate AND @Enddate

You don't have to convert the dates to make the BETWEEN statement work

answered Feb 26, 2013 at 10:04

jazzytomato

7,2593 gold badges33 silver badges44 bronze badges

2 Comments

James Khan Over a year ago

That gives error Msg 137, Level 15, State 2, Line 39 Must declare the scalar variable "@Startdate".

Ivan Golović Over a year ago

It doesn't work because this is a dynamic query which contains SELECT statement, it doesn't contain variable declarations, it can't see variables that are not declared in string containing the dynamic SQL.

Indi · Accepted Answer · 2013-02-26 10:17:25Z

0

I think there are a few of issues in your query, (1) forming up your sql string, (2) trying to compare date with varchar values and (3) converting string to date

For example it should be like;

declare @sql nvarchar(max), 
        @startdate varchar(50) = '20130202', --Using ISO format (yyyymmdd)
        @enddate varchar(50) = '20130226' --Using ISO format 

select @sql = 'SELECT col1, col2, ... FROM myTable WHERE mydate '+
               'between convert(date, ' + @startdate + ') and ' +
               'convert(date, ' + @enddate + ')'

answered Feb 26, 2013 at 10:17

Indi

33.9k7 gold badges61 silver badges80 bronze badges

Collectives™ on Stack Overflow

Dynamic sql syntax with dates

4 Answers 4

3 Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related