Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1138
  • Last Modified:

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.
0
sarika_patalay
Asked:
sarika_patalay
  • 4
  • 2
1 Solution
 
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
 
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
 
sarika_patalayAuthor Commented:
:)

Thanks works like a charm !!
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now