Problem with datetime part in dynamic query.

I am trying to execute (or print first) a dynamic query which involves passing datetime fields as variables. The following is my syntax:

DECLARE @YYYY AS VARCHAR(4)
DECLARE @MM AS VARCHAR(2)
DECLARE @DATE1 AS DATETIME
DECLARE @DATE2 AS DATETIME
SET @DATE1 = GETDATE()
SET @DATE2 = DATEADD(DD, -1, GETDATE())
SET @YYYY = '2007'
SET @MM = '09'

PRINT(' INSERT INTO #TRESHOLD_TABLE' +
' SELECT SOURCE_NUMBER, SUM(RETAIL_BASE_COST_1), CAST(SWITCH_ID AS INT)'+
' FROM '+'VI'+@YYYY+@MM +
' WHERE [ACTIVITY_DATE_TIME] > = '+@DATE2+' AND [ACTIVITY_DATE_TIME]<= '+@DATE1+
' AND ANSWERED = ''Y'' AND [RETAIL_RATE_FLAG_1] = ''S'''+
' GROUP BY SOURCE_NUMBER, SWITCH_ID')

When I execute the above, I get this message :
Msg 241, Level 16, State 1, Line 10
Conversion failed when converting datetime from character string.

If someone knows the solution pls reply.

Thanks.
sarika_patalayAsked:
Who is Participating?
 
cezarFCommented:
converted @DATE1 and  @DATE2 to nvarchar.  I would also suggest to use BETWEEN

DECLARE @YYYY AS VARCHAR(4)
DECLARE @MM AS VARCHAR(2)
DECLARE @DATE1 AS DATETIME
DECLARE @DATE2 AS DATETIME
SET @DATE1 = GETDATE()
SET @DATE2 = DATEADD(DD, -1, GETDATE())
SET @YYYY = '2007'
SET @MM = '09'

PRINT(' INSERT INTO #TRESHOLD_TABLE' +
' SELECT SOURCE_NUMBER, SUM(RETAIL_BASE_COST_1), CAST(SWITCH_ID AS INT)'+
' FROM '+'VI'+@YYYY+@MM +
' WHERE [ACTIVITY_DATE_TIME] BETWEEN '+ CONVERT(NVARCHAR(10),@DATE2,103) +' AND '+CONVERT(NVARCHAR(10),@DATE1,103)+
' AND ANSWERED = ''Y'' AND [RETAIL_RATE_FLAG_1] = ''S'''+
' GROUP BY SOURCE_NUMBER, SWITCH_ID')
0
 
cezarFCommented:
DECLARE @YYYY AS VARCHAR(4)
DECLARE @MM AS VARCHAR(2)
DECLARE @DATE1 AS DATETIME
DECLARE @DATE2 AS DATETIME
SET @DATE1 = GETDATE()
SET @DATE2 = DATEADD(DD, -1, GETDATE())
SET @YYYY = '2007'
SET @MM = '09'

PRINT(' INSERT INTO #TRESHOLD_TABLE' +
' SELECT SOURCE_NUMBER, SUM(RETAIL_BASE_COST_1), CAST(SWITCH_ID AS INT)'+
' FROM '+'VI'+@YYYY+@MM +
' WHERE [ACTIVITY_DATE_TIME] >= '+ CONVERT(NVARCHAR(10),@DATE2,103) +' AND [ACTIVITY_DATE_TIME]<= '+CONVERT(NVARCHAR(10),@DATE1,103)+
' AND ANSWERED = ''Y'' AND [RETAIL_RATE_FLAG_1] = ''S'''+
' GROUP BY SOURCE_NUMBER, SWITCH_ID')
0
 
sarika_patalayAuthor Commented:
:)

Thanks works like a charm !!
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
cezarFCommented:
thanks, just want to ask why "B" if it works like a charm?
:)
0
 
sarika_patalayAuthor Commented:
why 'B' you ask...ummm.....may be bacause,  I dont think the qn was 'Super' difficult. I did figure out that using a convert nvarchar stuff is required. Infact I tried the convert part even before I posted this question,  but where you bagged the price is by suggesting the use of word 'Between' .

You are good, infact very good, its just thst this qn does not qualify for 'Excellent'. Infact if I would have awarded you an 'A' for this qn, it would be undermining your abilities - what say ?
0
 
cezarFCommented:
thanks for the clarification.  

I was just anxious to know so that I can improve (if ever) and get an "A" next time.

cheers!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.