Date conversion error
Created: 22 Feb 2013 | 13 comments
here is an example of the intergration: Very basic
Update tblSupportRequests
Set
ClosedUser = @ClosedUservalue,
Closeddate = @ClosedDatevalue,
Status = @StatusValue,
Solution = @Solution
where RequestID=@RequestIDvalue
and we get the following error
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated
the closedDate value is coming from a currentdate component and the close user is a get current user component. Statue is a hard value of Closed, and Solution is an user imported field.
Operating Systems:
Discussion Filed Under:
Comments 13 Comments • Jump to latest comment
It sounds like parameters are crossed or something but the actual error including stack trace would help to diagnose the issue. What type of component are you using to do the update?
Depending on what your string closed date field looks like, you probably need to do some truncation/reformatting of the date value via a CAST/CONVERT. See this post:
http://msdn.microsoft.com/en-us/library/ms187928.aspx
Your SQL will look like the following. <my style here> depends on what the date output looks like per the previous link.
Set
ClosedUser = @ClosedUservalue,
Closeddate = CONVERT(DATETIME, @ClosedDatevalue, <my style here>),
Status = @StatusValue,
Solution = @Solution
where RequestID=@RequestIDvalue
I'd also run a SQL trace and verify exactly what SQL is being run (parameter values and all). Once you have the SQL being run, you can tweak it in SQL Query windows until it works, then adjust the SQL in your components.
here is the full error that I receive in the email
SupportRequestCloseTicket.SqlQuery.SupportRequestCloseTicket
SupportRequestCloseTicket
0bf5e572-f2c9-4bb2-a615-98c9ec9d6559
System.Data.SqlClient.SqlException
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated.
System.Data.SqlClient.SqlException: The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated. at SupportRequestCloseTicket.SqlQuery.SupportRequestCloseTicket.Run(IData data) at LogicBase.Core.ExecutionEngine.SinglePathProcessComponentExecutionDelegate.Execute(IData data, IOrchestrationComponent comp, String& outputPath, IExecutionEngine engine, TLExecutionContext context) at LogicBase.Core.ExecutionEngine.AbstractExecutionEngine.RunComponent(TLExecutionContext context, IData data, IOrchestrationComponent comp)
the update in an integration
I updated the integration with the cast command but the Process isnt giving the update compent when its open so I don't think it is taking it.
using the convert I now get this error
SupportRequestClosingTicket.SupportRequestClosingTicket
Support Request Closing Ticket
86d951bc-7d19-11e2-9554-005056a50013
System.Data.SqlClient.SqlException
Conversion failed when converting date and/or time from character string.
System.Data.SqlClient.SqlException: Conversion failed when converting date and/or time from character string. at SupportRequestClosingTicket.SupportRequestClosingTicket.Run(IData data) at LogicBase.Core.ExecutionEngine.SinglePathProcessComponentExecutionDelegate.Execute(IData data, IOrchestrationComponent comp, String& outputPath, IExecutionEngine engine, TLExecutionContext context) at LogicBase.Core.ExecutionEngine.AbstractExecutionEngine.RunComponent(TLExecutionContext context, IData data, IOrchestrationComponent comp)
Is there any way you can get a SQL trace of the SQL being run? Once I have the SQL being run, I can create a test table and give you the exact SQL you need.
If we run the Test query within in the complie of the intergation and hardcode the info it all works fine so it is obvisouly not the intergration compent that is the issue but maybe somtthing else. Even within the teest query the date goes in day/month/year and sql fixes it correctly back to yyyy-mm-dd not problem. I am wondering if it is the get current date that is being used to fill in the date field.
If I hardcode dates into that field and run the process it works fine so it is a problem when it uses the get current date component.
Is the date you're hardcoding similar in format to the output of the Get Current Date component? What does the output of your Get Current Date component look like?
My Get Current Date returned: 2/22/2013 2:34:52 PM
and when I ran: update AuditHistory set ModifiedDate = '2/22/2013 2:34:52 PM'
I got no errors. It could be that the Get Current Date comp is returning values in a format the SQL Server is not expecting.
I am hardcoding in DD/MM/YYYY
SQL wants changes that to YYYY-MM-DD without problem I believe my get current date is the same as yours So I wonder if its the Time that is throwing it off as the hardcoded date doesnt have a time. Sql in the past has set the times to be 0000000
If the time is throwing it off, and you're not tremendously worried about the time component, what I would do is run a Split Text Into Collection component immediately after running the Get Current Date. Use the Space value of the dropdown as the delimiter, and run it on the Get Current Date output. The 1st component of the resulting collection will be just the date portion of the current date.
Use that date portion in your SQL statement.
I have found the problme. My client machine was set to English Canada, the servers are set to english US, so when the get current date field gets it data it pulls it from the client that is running the process not that server. Seems rather messy if you have clients in differnet parts of the world. Does anyone know away around this?
There's probably an easier way of doing this, but you could "rebuild" the date parameter in SQL based on the parameter passed.
UPDATE Blah
SET MyDate = CAST(
CAST(DATEPART(year, @dateparm) AS VARCHAR) + '-' +
CAST(DATEPART(month, @dateparm) AS VARCHAR) + '-' +
CAST(DATEPART(day, @dateparm) AS VARCHAR)
AS DATETIME)
If rebuilding in SQL doesn't work, you can rebuild via similar components in Workflow
Hi
try with this in your convert
set dateformat dmy --this is for spanish people if you need for another format only change the order
take the last sql code this is the solution(i dont know is ok)
set dateformat dmy
UPDATE Blah
SET MyDate = CAST(
CAST(DATEPART(year, @dateparm) AS VARCHAR) + '-' +
CAST(DATEPART(month, @dateparm) AS VARCHAR) + '-' +
CAST(DATEPART(day, @dateparm) AS VARCHAR)
AS DATETIME)
Would you like to reply?
Login or Register to post your comment.