June 12, 2008 at 2:02 am
Hello All,
I'm facing a problem with the below query that I'm using in an SSRS 2005 report for MS CRM 4.0:
----------
declare @SQL nvarchar(4000)
declare @fromdate datetime, @todate datetime
set @fromdate = '6/16/2008'
set @todate = '7/22/2008'
set @SQL = N'declare @dealid uniqueidentifier, @date1 datetime, @date2 datetime; Select top 1 @dealid = new_dealid from (select * from Filterednew_deal where new_name = ''Boulevard Al Areen Real Estate'') Deals; '
+ 'set @date1 = ' + @fromdate + '; set @date2 =, ' + @todate + '; '
+ 'exec sp_PlannedVsActualActivities @dealid, @date1, @date2'
exec(@SQL)
when i try to run the above query it produce the following error:
Msg 241, Level 16, State 1, Line 5
Conversion failed when converting datetime from character string
I tried all the acceptable formats of datetime but it didn't work. I also tried all the possible CONVERT and CAST methods with no luck.
---------
when I replace the @fromdate and @todate with their actual values in the query, it works and run the stored procedure and display the required results. below is the new query:
declare @SQL nvarchar(4000)
declare @fromdate datetime, @todate datetime
set @fromdate = '6/16/2008'
set @todate = '7/22/2008'
set @SQL = N'declare @dealid uniqueidentifier, @date1 datetime, @date2 datetime; Select top 1 @dealid = new_dealid from (select * from Filterednew_deal where new_name = ''Boulevard Al Areen Real Estate'') Deals; '
+ 'set @date1 =''6/16/2008'';set @date2 = ''7/22/2008'';'
+ 'exec sp_PlannedVsActualActivities @dealid, @date1, @date2'
exec(@SQL)
----------
I tried also to exclude the @SQL from the query and kept the @fromdate and @todate in the query by removing the string concatenations, it works and run the stored procedure and display the required results. below is the new query:
declare @fromdate datetime, @todate datetime
set @fromdate = '6/16/2008'
set @todate = '7/22/2008'
declare @dealid uniqueidentifier, @date1 datetime, @date2 datetime;
Select top 1 @dealid = new_dealid from (select * from Filterednew_deal where new_name = 'Boulevard Al Areen Real Estate') Deals;
set @date1 = @fromdate; set @date2 = @todate;
exec sp_PlannedVsActualActivities @dealid, @date1, @date2
----------
Any ideas on handling this situation, on solving this problem?
It's so urgent; your help is really appreciated.
June 12, 2008 at 2:10 am
Try the yyyy/mm/dd date format.
set @fromdate = '2008/06/16'
set @todate = '2008/07/22'
Edit:
Oh, and this is also a problem
'set @date1 = ' + @fromdate + '; set @date2 =, ' + @todate + '; '
+ 'exec sp_PlannedVsActualActivities @dealid, @date1, @date2'
You need to cast the 2 date variables to varchar in order to concat them into the string. Maybe this...
'set @date1 = ' + CAST(@fromdate AS VARCHAR(26)) + ';
set @date2 =, ' + CAST(@todate AS VARCHAR(26)) + '; '
Come to think of it, why are you using dynamic SQL at all? This should work just fine
declare @fromdate datetime, @todate datetime, @dealid uniqueidentifier
set @fromdate = '6/16/2008'
set @todate = '7/22/2008'
Select top 1 @dealid = new_dealid from Filterednew_deal
where new_name = 'Boulevard Al Areen Real Estate';
exec sp_PlannedVsActualActivities @dealid, @fromdate , @todate
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2008 at 2:31 am
Hi Gail,
thanks for the reply.
i tried the date format that you suggsted but it still producing the same error.
i have already mentioned that i tried all the possible formats of date.
the @formdate and @todate are in reality datepicker parameters; i have just added the following section to the query to test it in SQL Manager:
declare @fromdate datetime, @todate datetime
set @fromdate = '2008/06/16'
set @todate = '2008/07/22'
anyway, you can notice that the last query of my initial post that it's the same query; i have just removed the @sql variable, the single quotes and the + signs that were used for concatenation.
when i run this query it works fine and display the required results.
I think the problem is caused by the string concatenation of @SQL variable.
do you have any idea how we can solve this issue?
June 12, 2008 at 2:33 am
See my edited post.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2008 at 3:13 am
Hi again Gail,
Sorry, I didn't notice that you edited your previous post.
I tried what you suggested in your edited post with a little change to the syntax and it's now working like charm.
you need to replace 'set @date1 = ' + CAST(@fromdate AS VARCHAR(26)) + '; with 'set @date1 = ''' + CAST(@fromdate AS VARCHAR(26)) + ''';. below is the final query:
declare @SQL nvarchar(4000)
declare @fromdate datetime, @todate datetime
set @fromdate = '6/16/2008'
set @todate = '7/22/2008'
set @SQL = N'declare @dealid uniqueidentifier, @date1 datetime, @date2 datetime; Select top 1 @dealid = new_dealid from (select * from Filterednew_deal where new_name = ''Boulevard Al Areen Real Estate'') Deals; '
+ 'set @date1 = ''' + CAST(@fromdate AS VARCHAR(26)) + '''; set @date2 = ''' + CAST(@todate AS VARCHAR(26)) + '''; '
+ 'exec sp_PlannedVsActualActivities @dealid, @date1, @date2'
exec(@SQL)
thank you Gail; I spent yesterday the whole day to find a solution for this problem with no luck.
thanks again for your help; it's really appreciated.
June 12, 2008 at 3:17 am
Pleasure.
I'm still unclear why you're using dynamic SQL at all. There doesn't seem to be a need.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 7, 2010 at 9:26 pm
Hi there,
Just thought I would post an alternative solution to a similar issue I was having with moving UK date styles in MS Access Strings to MS SQL Server 2005.
I was getting that bloody error even if I separated the date out into parts and concatenated it back together in yyyy-mm-dd ISO Format, so I used a INTO statement and then converted the DATEFORMAT back to "normal" and then ran the CAST on the ISO Standard format.
Worked a treat.
SET DATEFORMAT dmy
SELECT id.intInspectionDetailsID,
CONVERT(varchar(10), CAST(DATEPART(YEAR, wp.DateInspected) as varchar) + '/' + CAST(DATEPART(MONTH, wp.DateInspected) as varchar) + '/' + CAST(DATEPART(DAY, wp.DateInspected) as varchar), 126) as DateInspectedConv
INTO #Temp
FROM xxx.dbo.WP_Vemco_Poles wp INNER JOIN xxxUAT.dbo.Span sp ON wp.vchSpanRefVegZoneYear = sp.vchSpanRefVegZoneYear
INNER JOIN InspectionDetails id ON sp.intSpanID = id.intSpanID
SET DATEFORMAT mdy
--SELECT CAST(CONVERT(varchar, DateInspectedConv, 126) as datetime)
UPDATE id
SET id.Date = CAST(CONVERT(varchar, tmp.DateInspectedConv, 126) as datetime)
FROM #Temp tmp INNER JOIN InspectionDetails id ON id.intInspectionDetailsID = tmp.intInspectionDetailsID
DROP TABLE #Temp
December 14, 2010 at 7:21 am
Gila & FN thanks for the posting, but I'm having a similar but continuing to be odd problem with a date conversion.
My problem arises when I include the INSERT INTO section of my procedure listed below.
The dates when selected through TSQL all represent appropriate dt forms/formatting without giving an error.
So here's the nutshell overview of what's below:
I am building a fact structure called cube_shipped which is a culmination of orders booked and those shipped for a given monthly cycle. In lieu of running heavy bulk loading of the year's records I'm cycling through the source tables by customer and date range (for reasons beyond this posting.) I know there are always different ways of solving a problem but this works and had worked for several months until the users wanted more data, thus the wider ranging and date driver.
I set the variables I'll need to loop through the source records than then select the records given the parameters I need then try to insert them.
ONLY when I use the INSERT portion does the procedure bail/fail (line 100 is the entry line of the INSERT INTO statement) with the error message:
(448 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
Msg 241, Level 16, State 1, Procedure usp_whse_cube_shipped_step1, Line 100
Conversion failed when converting datetime from character string.
/******************************************************/
ALTER PROCEDURE [dbo].[usp_whse_cube_shipped_step1]
as
/*******************************************************
Author: JBJ
Date:3/09/2010
Notes: Revised to expand by including new columns and dropping old
(see requirements doc from sales)
NOTE: Always validate a backup has completed prior to major data changes
Date:09/06/2010 - CONVERTED FROM OLD VB.6 PROGRAM !!!
Date: 12/12/2010 - Need to revise loading to speed up and break down the process
- added several more indices and the customer and date driven parameters
- to the procedure
********************************************************/
DECLARE @fromdate DATETIME
DECLARE @todate DATETIME
DECLARE @loopfrom DATETIME
DECLARE @loopto DATETIME
DECLARE @cust_cnt INT
DECLARE @dateloop INT
DECLARE @C INT -- loop counter customers
DECLARE @d INT -- loop counter date ranges by month
DECLARE @custid VARCHAR(30)
SET @fromdate = (SELECT convert(varchar(10),'20100801',112))
SET@todate= (SELECT convert(varchar(10),GETDATE(),112))
SET@dateloop= (SELECT DATEDIFF(mm,@fromdate,@todate))
SET@d = 1
BEGIN
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CUBE_SHIPPED_UPD]'))
CREATE TABLE [dbo].[CUBE_SHIPPED_UPD](
[SYSTEM_ID] [varchar](15) NULL,
[CUSTOMER_ID] [varchar](15) NULL,
[CUST_ORDER_ID] [varchar](15) NULL,
[PRODUCT_CODE] [varchar](15) NULL,
[COMMODITY_CODE] [varchar](15) NULL,
[PART_ID] [varchar](30) NULL,
[INVOICE_DATE] [datetime] NULL,
[BRAND] [varchar](15) NULL,
[TERRITORY] [varchar](15) NULL,
[REPID] [varchar](80) NULL,
[STATUS] [char](1) NULL,
[UNIT_PRICE] [decimal](20, 6) NULL,
[AMOUNT] [decimal](15, 2) NULL,
[QUANTITY] [decimal](14, 4) NULL,
[COST] [decimal](14, 4) NULL,
[QUOTE_ID] [varchar](80) NULL,
[APEX_ACCESS_CODE] [varchar](50) NULL,
[APEX_DISCOUNT] [decimal](6, 3) NULL,
[SHIP_TO_ADDR_NO] [int] NULL,
[SHIP_TO_ID] [varchar](20) NULL,
[PRICING_IND] [varchar](80) NULL,
[FABRICATED] [char](1) NULL,
[PURCHASED] [char](1) NULL,
[DESIRED_SHIP_DATE] [datetime] NULL,
[PROMISE_DATE] [datetime] NULL,
[PART_ID_COMP] [varchar](100) NULL,
[ORDER_ID_COMP] [varchar](100) NULL,
[SHIP_TO_COMP] [varchar](100) NULL,
[PART_ID_DESCRIPT] [varchar](100) NULL,
[ADDR_1] [varchar](50) NULL,
[ADDR_2] [varchar](50) NULL,
[CITY] [varchar](50) NULL,
[STATE] [varchar](50) NULL,
[ZIPCODE] [varchar](50) NULL,
[COUNTRY] [varchar](50) NULL,
[REPORT_GROUP] [varchar](50) NULL,
[CATEGORY] [varchar](50) NULL,
[FAMILY] [varchar](50) NULL,
[CHANNEL] [varchar](50) NULL,
[GL_REVENUE_ACCT] [varchar](50) NULL,
[ACTIVE_FLAG] [varchar](1) NULL,
[PROGRAM_GROUP_ID] [varchar](80) NULL,
[CUST_ORDER_LINE_NO] [varchar](15) NULL
)
TRUNCATE TABLE [dbo].[CUBE_SHIPPED_UPD]
END
IF object_id('tempdb..#tmp_customer_list') is not null DROP TABLE [#tmp_customer_list]
SELECT DISTINCT IDENTITY(INT,1,1) AS cust_num,customer_id INTO #tmp_customer_list FROM CUSTOMER_ORDER O
WHERE O.ORDER_DATE >= '20100801' AND O.CUSTOMER_ID NOT LIKE 'AMEB%' AND O.CUSTOMER_ID NOT LIKE 'MEX-%' AND O.CUSTOMER_ID NOT LIKE 'SAF-%'
ORDER BY O.CUSTOMER_ID
SET @cust_cnt = @@ROWCOUNT
BEGIN
SET @C = 1
--SET @C = 168 -- for testing a specific customer
WHILE @C <= @cust_cnt
BEGIN
SET @custid = (select customer_id from #tmp_customer_list where cust_num = @C)
SET @d = 1
WHILE @d <= @dateloop
BEGIN
SET @loopfrom = (SELECT DATEADD(DD,0,DATEADD(MM,DATEDIFF(MM,0,DATEADD(MM, -((@dateloop+1)-@d),@todate)),0)))
SET @loopto = (SELECT DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,0,@loopfrom)+1,0)))
--select @C, @custid, @fromdate, @todate, @dateloop, @d, @cust_cnt, @loopfrom, @loopto,
--BEGIN TRANSACTION
INSERT INTO CUBE_SHIPPED_UPD
([SYSTEM_ID]
,[CUSTOMER_ID]
,[CUST_ORDER_ID]
,[PRODUCT_CODE]
,[COMMODITY_CODE]
,[PART_ID]
,[INVOICE_DATE]
,[BRAND]
,[TERRITORY]
,[REPID]
,[STATUS]
,[UNIT_PRICE]
,[AMOUNT]
,[QUANTITY]
,[COST]
,[QUOTE_ID]
,[APEX_ACCESS_CODE]
,[APEX_DISCOUNT]
,[SHIP_TO_ADDR_NO]
,[SHIP_TO_ID]
,[PRICING_IND]
,[FABRICATED]
,[PURCHASED]
,[DESIRED_SHIP_DATE]
,[PROMISE_DATE]
,[PART_ID_COMP]
,[ORDER_ID_COMP]
,[SHIP_TO_COMP]
,[GL_REVENUE_ACCT]
,[PART_ID_DESCRIPT]
,[CUST_ORDER_LINE_NO]
)
-- CUBE_SHIP COLUMNS NOT INCLUDED INITIALLY
/******************************************
,[PART_ID_DESCRIPT]
,[ADDR_1]
,[ADDR_2]
,[CITY]
,[STATE]
,[ZIPCODE]
,[COUNTRY]
,[REPORT_GROUP]
,[CATEGORY]
,[FAMILY]
,[CHANNEL]
,[ACTIVE_FLAG]
,[PROGRAM_GROUP_ID]
********************************************/
SELECT -- @custid, @loopfrom, @loopto, @fromdate, @todate, @dateloop, @cust_cnt, @d, @C, --DISTINCT
O.SYSTEM_ID
,O.CUSTOMER_ID
,O.ID order_id
,isnull(OL.PRODUCT_CODE,'None') as product_code
,isnull(OL.COMMODITY_CODE,'None') as commodity_code
,isnull(OL.PART_ID, 'None') as part_id
,isnull(P.DESCRIPTION,'None') as part_id_descript
,R.POSTING_DATE as invoice_date
,isnull(P.USER_1,isnull(ABP.brandcode,'not defined')) AS brand
,isnull(O.TERRITORY,'unknown') as territory
,isnull(O.SALESREP_ID,'not defined') salesrep
,O.STATUS ORDER_STATUS
,OL.UNIT_PRICE
,RL.AMOUNT INVOICED_AMOUNT
,RL.QTY INVOICE_qty
,isnull((IT.ACT_MATERIAL_COST + IT.ACT_LABOR_COST + IT.ACT_BURDEN_COST + IT.ACT_SERVICE_COST),isnull((P.UNIT_MATERIAL_COST + P.UNIT_LABOR_COST + P.UNIT_BURDEN_COST + P.UNIT_SERVICE_COST),0)) as cost
,isnull(OL.USER_6,'not found') as quote_id
,isnull(OL.USER_8,'not found') as apex_access_code
,OL.TRADE_DISC_PERCENT as apex_discount
,isnull(O.SHIP_TO_ADDR_NO,'0') as ship_to_addr
,isnull(O.SHIPTO_ID,'None') as shipto_id
,O.USER_3 as pricing_ind
,isnull(P.FABRICATED,'X')as fabricated
,isnull(P.PURCHASED,'X')as purchased
,O.DESIRED_SHIP_DATE as desired_ship_date
,O.PROMISE_DATE as promise_date
,(O.SYSTEM_ID + '_' + OL.PRODUCT_CODE + '_' + OL.COMMODITY_CODE + '_' + OL.PART_ID) as part_id_comp
,(O.SYSTEM_ID + '_' + O.ID) as order_id_comp
,(O.SYSTEM_ID + '_' + O.CUSTOMER_ID + '_' + convert(varchar(30),isnull(O.SHIP_TO_ADDR_NO,'0')) + '_' + convert(varchar(30),isnull(O.SHIPTO_ID,'None'))) as ship_to_comp
,ISNULL(OL.GL_REVENUE_ACCT_ID,ISNULL(RL.GL_ACCOUNT_ID, PR.REV_GL_ACCT_ID)) AS GL_ACCT_ID
,OL.LINE_NO
--
--,O.ORDER_DATE
--,OL.ORDER_QTY
--,OL.TOTAL_AMT_ORDERED
--,R.INVOICE_ID
--,R.STATUS INVOICE_STATUS
--,RL.LINE_NO
--,S.PACKLIST_ID
--,S.SHIPPED_DATE
--,SL.LINE_NO
--,SL.SHIPPED_QTY
--,SL.USER_SHIPPED_QTY as shipped_quantity
--
FROM CUSTOMER_ORDER O
INNER JOIN CUST_ORDER_LINE OL ON O.SYSTEM_ID = OL.SYSTEM_ID
AND O.ID = OL.CUST_ORDER_ID
INNER JOIN SHIPPER S ON O.SYSTEM_ID = S.SYSTEM_ID
AND O.ID = S.CUST_ORDER_ID
INNER JOIN SHIPPER_LINE SL ON S.CUST_ORDER_ID = SL.CUST_ORDER_ID
AND OL.LINE_NO = SL.LINE_NO
AND S.PACKLIST_ID = SL.PACKLIST_ID
INNER JOIN RECEIVABLE R ON O.SYSTEM_ID = R.SYSTEM_ID
AND O.CUSTOMER_ID = R.CUSTOMER_ID
INNER JOIN RECEIVABLE_LINE RL ON R.INVOICE_ID = RL.INVOICE_ID
AND O.SYSTEM_ID = RL.SYSTEM_ID
AND OL.CUST_ORDER_ID = RL.CUST_ORDER_ID
AND OL.LINE_NO = RL.LINE_NO
AND SL.LINE_NO = RL.PACKLIST_LINE_NO
AND SL.PACKLIST_ID = RL.PACKLIST_ID
LEFT OUTER JOIN PART P ON OL.SYSTEM_ID = P.SYSTEM_ID
AND OL.PART_ID = P.ID
LEFT OUTER JOIN PRODUCT PR ON OL.SYSTEM_ID = PR.SYSTEM_ID
AND P.SYSTEM_ID = PR.SYSTEM_ID
AND P.PRODUCT_CODE = PR.CODE
LEFT OUTER JOIN INVENTORY_TRANS IT ON O.SYSTEM_ID = IT.SYSTEM_ID
AND O.ID = SL.CUST_ORDER_ID
AND OL.LINE_NO = SL.CUST_ORDER_LINE_NO
AND SL.SYSTEM_ID = IT.SYSTEM_ID
AND SL.TRANSACTION_ID = IT.TRANSACTION_ID
LEFT OUTER JOIN AHI_APEX_BRAND_PRODUCTCOMMODITY ABP ON isnull(OL.PRODUCT_CODE,' ') = isnull(ABP.PRODUCTCODE,' ')
AND isnull(OL.COMMODITY_CODE,' ') = isnull(ABP.COMMODITYCODE, ' ')
AND OL.SYSTEM_ID = ABP.[SID]
--
WHERE R.POSTING_DATE >= @loopfrom
AND R.POSTING_DATE <= @loopto
AND O.CUSTOMER_ID = @custid -- used for isolating records for verification
AND O.ID NOT LIKE 'MEX%'
AND OL.PRODUCT_CODE NOT LIKE 'IC%'
GROUP BY
O.SYSTEM_ID
,O.CUSTOMER_ID
,O.ID
,isnull(OL.PRODUCT_CODE,'None')
,isnull(OL.COMMODITY_CODE,'None')
,isnull(OL.PART_ID, 'None')
,isnull(P.DESCRIPTION,'None')
,R.POSTING_DATE
,isnull(P.USER_1,isnull(ABP.brandcode,'not defined'))
,O.TERRITORY
,O.SALESREP_ID
,O.STATUS
,OL.UNIT_PRICE
,RL.AMOUNT
,RL.QTY
,isnull((IT.ACT_MATERIAL_COST + IT.ACT_LABOR_COST + IT.ACT_BURDEN_COST + IT.ACT_SERVICE_COST),isnull((P.UNIT_MATERIAL_COST + P.UNIT_LABOR_COST + P.UNIT_BURDEN_COST + P.UNIT_SERVICE_COST),0))
,isnull(OL.USER_6,'not found')
,isnull(OL.USER_8,'not found')
,OL.TRADE_DISC_PERCENT
,isnull(O.SHIP_TO_ADDR_NO,'0')
,isnull(O.SHIPTO_ID,'None')
,O.USER_3
,isnull(P.FABRICATED,'X')
,isnull(P.PURCHASED,'X')
,O.DESIRED_SHIP_DATE
,O.PROMISE_DATE
,(O.SYSTEM_ID + '_' + OL.PRODUCT_CODE + '_' + OL.COMMODITY_CODE + '_' + OL.PART_ID)
,(O.SYSTEM_ID + '_' + O.ID)
,(O.SYSTEM_ID + '_' + O.CUSTOMER_ID + '_' + convert(varchar(30),isnull(O.SHIP_TO_ADDR_NO,'0')) + '_' + convert(varchar(30),isnull(O.SHIPTO_ID,'None')))
,ISNULL(OL.GL_REVENUE_ACCT_ID,ISNULL(RL.GL_ACCOUNT_ID, PR.REV_GL_ACCT_ID))
,OL.LINE_NO
--COMMIT
SET @d = @d + 1 -- @dateloop + 1
END
SET @C = @C + 1 -- @cust_cnt + 1
END
END
/***************************************************/
Boy do I feel like a marooon! :Whistling:
I found my problem in the code. In my haste I failed to follow coding rule 101:
Always make sure your insert list is matched by your select list: Failure will give all sorts of data formatting errors when data is not lined up!!
Just looking hard and long at something won't make it work
Sorry for the confusion!!: w00t:
October 26, 2012 at 8:12 am
Thanks Gail, your solution help me a lot for which I was struggling last 3 days 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply