Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

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:

Comments 13 CommentsJump to latest comment

rhamner's picture

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?

reecardo's picture

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.

Dkromm's picture

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.

Dkromm's picture

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)

reecardo's picture

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.

Dkromm's picture

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.

Dkromm's picture

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.

reecardo's picture

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.

Dkromm's picture

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

reecardo's picture

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.

Dkromm's picture

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?

reecardo's picture

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

Jorge Fernandez's picture

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)