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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy