asked on

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.

cezarF

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

ASKER CERTIFIED SOLUTION

cezarF

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sarika_patalay

ASKER

:)

Thanks works like a charm !!

cezarF

thanks, just want to ask why "B" if it works like a charm?
:)

sarika_patalay

ASKER

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 ?

cezarF

thanks for the clarification.

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

cheers!