The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Importing date fields
Posted: 11 Apr 06 3:28 AM
|
fiogf49gjkf0d Hi there, I'm having a problemwith something fairly straightforward - importing date fields into SLX...
I've formatted the field in my csv to be YYYY-MM-DD, this being the standard format in my experience for SQL (well, MySQL)... is this the same for MS SQL?
I have attempted mapping the field directly and also tried running it through the various functions available in the import wizard (StringAsTime, GMCreateDate and IntegerAsTime) just to see if they would work - to no avail.
Has anyone got experience with this before? Any help much appreciated! |
|
|
|
Re: Importing date fields
Posted: 11 Apr 06 4:42 PM
|
fiogf49gjkf0d What are you using the do the import? (which tool?)
Also, what exactly is going wrong? What is the problem you're having with the imported dates? (ie: error, invalid date value once imported, etc - please describe the problem) |
|
|
|
Re: Importing date fields
Posted: 11 Apr 06 7:28 PM
|
abortion clinics near metairie la abortion clinics near metairie la go fiogf49gjkf0d Hi Ryan, thanks for the reply.
I'm using the SLX Import Wizard which is run through ImportWiz.exe in the Program Files\SalesLogix folder. When I attempt to import the dates in the methods described above through the wizard, they simply do not appear in the date fields they are mapped to - the field is blank. Inspecting the database directly yields the same result - the field is simply blank.
Cheers
Pat |
|
|
|
Re: Importing date fields
Posted: 12 Apr 06 11:02 AM
|
fiogf49gjkf0d While I don't use the import wizard often, I have imported data with dates before using that and never had to do anything special with the values. Just left them as they were and dumped them in.
You don't get any errors listed in the log? |
|
|
|
Re: Importing date fields
Posted: 13 Apr 06 2:54 AM
|
fiogf49gjkf0d Nothing in the log at all - simply disappears from what I can tell... bit mysterious really. Would the YYYY-MM-DD format be a problem (ie, should there also be hours, minutes, seconds)? My experience with MySQL suggests not, but I'm not sure how translatable that is. |
|
|
|
Re: Importing date fields
Posted: 14 Apr 06 2:21 PM
|
fiogf49gjkf0d Do try adding the time to the date field. It does seem to work more consistantly that way.
Also, keep in mind that SalesLogix defaults to Greenwich Mean Time.
Instead of importing the time at 0 you may want to use the following format: '2006-04-14 06:01:00.000'.
We have had the datetime import correctly to the SalesLogix database field and then display the previous days date on screen. |
|
|
|
Re: Importing date fields
Posted: 18 Apr 06 3:29 AM
|
fiogf49gjkf0d Thanks for that Deb - based on your suggestion I have finally managed to get a date into the database via the import wizard in the format you suggested... however, would you believe that from an input field of '2005-11-10 10:01:00.000' it is displaying '30/12/1899' ?!? This seems to suggest that it is close but that I am doing something not quite right.
One thing to note is that the data being imported is delimited only by the comma character, there are no inverted comma delimiters.
Cheers, PS |
|
|
|
Re: Importing date fields
Posted: 19 Apr 06 9:14 AM
|
fiogf49gjkf0d Does it give you that on all of the dates or just some of them?
It looks as if you are getting the display format of dd/mm/yyyy and your import is changing 11/10/2005 to 12/30/1899. This could be a SQL thing. We got that error when we imported our original Progress database into SQL. (Before SalesLogix.) We had an Access database that also did that. They did not change every date.
We saw this most commonly if there was an empty field coming into the table. It also would occur when there was invalid data in the field. There may be a character that SQL does not understand and rather than leave the the field blank it sets the value to a default date. We discovered there were some records with incorrect / invalid data in the date field.
We looked for what was common to the dates that were changed and fixed them. So our data was OK by the time we imported it into SalesLogix.
I have never used the SalesLogix import wizard. I looked at it once and it did not appear to be able to do the maintenance imports we needed. We used Scribe software to do our original import into SalesLogix. I created SQL procedures to import the data that did not fit into the Scribe imports and for the maintenance imports we do. |
|
|
|
Re: Importing date fields
Posted: 23 Apr 06 6:03 PM
|
venlafaxine alcohol interaction venlafaxine alcohol fiogf49gjkf0d Thanks again Deb, that's done it - formatting the dates to dd-mm-yyyy hh:mm:ss:000 did the trick. Cheers, Pat. |
|
|
|