Issues with Converting Dates from yyyymmdd to yyyy-mm-dd HH:mm:ss.sss and back again (8 replies)
Sean, if you're trying to set the values of a date field in a Sage300 database, try the following:
- Set the field type to Text.
- The form of date field for inserting into a Sage300 database doesn't include dashes, so change your Format command to:
Format(%EXPIRE, "yyyyMMdd") 'Note upper chase M is month, lower case m is minutes.
Format(%EXPIRE, "yyyyMMdd") 'Note upper chase M is month, lower case m is minutes.
The initial issue is raised if I try to Convert a “20150811” type date to a DateTime String using a field type of “Text”….
I get the error 'Error whilst evaluating field [crm_start_date] on transaction type [By_CAT_item].
Function Resolved - crm_start_date
Error: Derscription: Overflow
Sean, what are you trying to do? If you are trying to convert from an Sage300 database date to a normal date then use the function:
NumberToDate(%EXPIRE) 'This is one of our functions
NumberToDate(%EXPIRE) 'This is one of our functions
Otherwise if you're converting from a date to a Sage300 database date type you need to have the EXPIRE field type as Text, then try using the Format command. It's worth nothing date handling was improved in version 3, so that you don't have to do this.
Thanks
Just a +1 on the below. This helped me get a string-formatted YYYYMMDD date from Sage 300 ERP into Sage CRM. Thanks.
NumberToDate(%MYDATE)
NumberToDate(%MYDATE)
Arline, for CRM if you set the field to a date type, IMan will auto-format it for CRM.
That wasn't my experience for some reason. I tried to identify it as a date during the Field Mapping transform, and the field was ignored. But if I added the NumberToDate during the Field Mapping transform, it worked.
Closed due to inactivity.
For the life of me I can't figure this out and I know it should be super simple.
Sage 300 stores dates in 8char strings, my CRM stores in them in true Date/Time, I'm working on a sync that is only working with the Contract Price table (not directly supported by Iman) so this is a MSSQL to MSSQL transform but the date formats are killing me.
This gives me the current date formatted in DateTime MSSQL style:
Result = Format(Date,"yyyy-MM-dd HH:mm:ss.sss")
If I try and pass it a value in place of "Date" like below I get an overflow.
Result = Format(%EXPIRE, "yyyy-mm-dd")
It's probably easy but I'm at a loss...