CODEWITHSUNDEEP
sqlsql-server
James Khan
James Khan

Reputation: 841

Dynamic sql syntax with dates

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

Upvotes: 3

Views: 4848

Answers (4)

Kaf
Kaf

Reputation: 33839

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 + ')'

Upvotes: 0

Ivan Golović
Ivan Golović

Reputation: 8832

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 ''.

Upvotes: 3

mson
mson

Reputation: 7822

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) +'''

Upvotes: 1

jazzytomato
jazzytomato

Reputation: 7239

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

Upvotes: 0

Related Questions