How to convert date in syclo agentry?

[sravanthi_polu]

Hi All,

I have implemented fetch mechanism to retrieve data to the frontend,but the date field values are not displaying .The datatype for the date field in frontend(agentry) and backend(non-sap) is date.I have done modifications in server file 'sqlserver_sd' to support different date formats.It is not showing any error in the query. when i executed that query in backend,it is showing the date in my required format ,but in ATE(version 6.0) it is not displaying.I thought to debug the fetch,but could not find the option in ATE to debug fetch.When i check in 'backend-sql' file ---found the error like

"Failed to set Date :>Object-"TItem">"Date" from db string (cannot convert) (currently )".Iam updating date as 'mm/dd/yyyy', in backend it is storing as 'yyyy/mm/dd'. What may be the possible solution?

Thanks in advance.

Regards,

Sravanthi Polu

Tags edited by: Michael Appleby

[jason_latko]

Sravanthi,

What database are you using?  You shouldn't need to do anything with your sqlserver_sd file to accomplish this.  If you have a date stored as a string in the backend, you need to use a database function to convert that string to a date or datetime type and Agentry should then consume it properly without issue.  In SQL Server you could use CAST or CONVERT.  In Oracle you could use TO_DATE.

Jason Latko - Senior Product Developer at SAP

[sravanthi_polu]

Hi Jason,

Thanks for your reply.I have included different date formats in sqlserver_sd ,these modifications i have done for updating date through transactions.For downloading i have not done any changes in that file.While selecting data i have used the statement like "select CONVERT(varchar(10),date,101) from TrainingPlanItems1",this statement is retrieving date in required format in backend. But when i used this statement in read steps it is showing the above error in 'backend-sql' file "Failed to set Date :>Object-"TItem">"Date" from db string (cannot convert) (currently )", but remaining fields data it is retrieving,the problem is only with date field.

Regards,

Sravanthi Polu.

[jason_latko]

Sravanthi,

Another user is having the same problem.  Please refer to my answer there:

http://scn.sap.com/thread/3458007

Jason Latko - Senior Product Developer at SAP

[sravanthi_polu]

Jason,

Thanks for your reply .I have tried the solution proposed but could not achieve the required result.I have tried with all possible convert statements.Is there any alternative way,like any date property settings in application or can i debug fetches steps.

Regards,

Sravanthi Polu

[jason_latko]

Sravanthi,

There is no way to "debug" a SQL fetch, other than to review the log and verify the query works as you already did.

I just tried this with the code I provided earlier.  It is working fine for me with a SQL Server backend.  I added a new Date type property to my ServiceOrder Agentry Object called "DateTest".  I also display the Date on screen after the fetch to verify.  Below is the SQL Log after transmit:

2013/11/26 11:53:57.285:             + Query=Fetch "ServiceOrderFetch" ServerExchange "DateTest"

2013/11/26 11:53:57.285:               : 3 lines, 2 non-printing characters, 134 total characters

SELECT

'005056BA79B11ED3909928F44EEB50F4' as serviceorderguid,

convert(datetime, convert(varchar(10), getdate(), 112)) as DateTest

2013/11/26 11:53:57.395:               Query executed OK

2013/11/26 11:53:57.395:             Parsing Fetch "ServiceOrderFetch" ServerExchange "DateTest" for Object Properties

2013/11/26 11:53:57.395:             Module: CRM

2013/11/26 11:53:57.395:             Root Object: ServiceOrder

2013/11/26 11:53:57.395:             ServiceOrder: Unique ID: ServiceOrderGUID

2013/11/26 11:53:57.405:             54 properties

2013/11/26 11:53:57.405:             Query returned 2 columns

2013/11/26 11:53:57.405:             + Column=DateTest

2013/11/26 11:53:57.405:               Column DateTest (2) <-> Property DateTest (68)

2013/11/26 11:53:57.405:             + Column=serviceorderguid

2013/11/26 11:53:57.405:               Column serviceorderguid (1) <-> Unique ID ServiceOrder:ServiceOrderGUID

2013/11/26 11:53:57.405:             + Row=1

2013/11/26 11:53:57.405:               + Column=serviceorderguid

2013/11/26 11:53:57.405:                 Column serviceorderguid (1): Set String :>Object-"ServiceOrder">"ServiceOrderGUID" to 005056BA79B11ED3909928F44EEB50F4 from db string '005056BA79B11ED3909928F44EEB50F4'

2013/11/26 11:53:57.405:               Reading properties

2013/11/26 11:53:57.405:               + Column=DateTest

2013/11/26 11:53:57.405:                 Column DateTest (2): Set Date :>Object-"ServiceOrder">"DateTest" to 11/26/2013 from db datetime 2013-11-26T00:00:00

2013/11/26 11:53:57.405:             1 row was processed

2013/11/26 11:53:57.405:             Parsing Fetch "ServiceOrderFetch" ServerExchange "DateTest" complete

2013/11/26 11:53:57.405:             Query results complete

Here is my query:

SELECT

'005056BA79B11ED3909928F44EEB50F4' as serviceorderguid,

convert(datetime, convert(varchar(10), getdate(), 112)) as DateTest

The above will return today's date.  You can replace getdate() above with any valid datetime column from the database.

Here is the log setting my DateTest property during the fetch:

2013/11/26 11:53:57.405:                 Column DateTest (2): Set Date :>Object-"ServiceOrder">"DateTest" to 11/26/2013 from db datetime 2013-11-26T00:00:00

I am using Agentry 6.1.3 and an iPad, but this code should work on any version of Agentry.  Please double check to verify you are doing things as I am here.

Jason Latko - Senior Product Developer at SAP

[sravanthi_polu]

Jason,

Thanks for your reply. I have modified "date" datatype to "datetime" in backend(mssql) and without any convert statements data has been retrieved correctly.

Regards,

Sravanthi Polu