BosaNova Transfer utility - S.O. S.

[vtorresviera]

I am trying to transfer a csv format text file converted from an Excells spreadsheet and i am having problems with the date fields. Not able to send the correct values to the AS/400. Can someone tell me how to define the date field in both sides.

Pleas help, S.O.S

[bryce4president]

Re: BosaNova Transfer utility - S.O. S.

Are you using the Excel Add-on to do it?

[jamief]

Re: BosaNova Transfer utility - S.O. S.

nice to see ya back

Great article on this ==> read article from Jeff Haddix

In order for Microsoft's Excel spreadsheet to recognize the set of values
for a column as dates, those values must be stored as a number of days.
For example, to export the date December 26, 2006, to an Excel spreadsheet,
you should export the number 37,066, not a date value like
12/26/2006 or 2006-12-26. However, due to an acknowledged defect in Excel,
be aware of the following.

Even though Excel claims that 01/01/1900 equates to Day 1,
for most purposes it is necessary to actually use 12/31/1899 as Day 1.
Otherwise, a one-day discrepancy will occur when converting IBM DB2 dates
(where Day 1 is 01/01/0001) after 02/28/1900 into Excel.

The reason is that Excel does not accurately apply the leap year
calculation, while IBM does. The frequently ignored part of the
calculation is that century years (1700, 1800, 1900, and so forth)
are not leap years, even though they are multiples of four.
The exception to that rule is that every 400 years (1200, 1600, 2000, etc.)
the century year is a leap year.

As such, February 1900 only had 28 days. However Excel erroneously
includes a 29th day, thus creating a one-day offset for all
subsequent date conversions.

In the simple test below, a short series of integers was keyed and then
those cells were formatted as dates. Notice that Excel improperly converts
day 60 to 2/29/1900, which is a non-existent date, thus creating a one-day
offset for all subsequent integer date conversions.
The following SQL query demonstrates how to derive Excel compatible dates from columns defined as either legacy packed-decimal date values or with a DATE datatype.

select   DECIMAL_DATE,                                           
           days( date( substr(digits(DECIMAL_DATE),1,4) || '-' ||
                       substr(digits(DECIMAL_DATE),5,2) || '-' ||
                       substr(digits(DECIMAL_DATE),7,4) ) )      
         - days( date('1899-12-30') )  as DAYS_FROM_DECIMAL_DATE,
         ISO_DATE,                                               
           days( ISO_DATE)                                       
         - days( date('1899-12-30') )  as DAYS_FROM_ISO_DATE     
from     DATETABLE 
order by 1 desc 

This will return a result set similar to the following.

DECIMAL_DATE  DAYS_FROM_DECIMAL_DATE  ISO_DATE    DAYS_FROM_ISO_DATE
 2006/12/26              39,077       2006-12-26            39,077  
 2006/12/17              39,068       2006-12-17            39,068  
 2006/12/01              39,052       2006-12-01            39,052  
 2006/02/01              38,749       2006-02-01            38,749  
 2006/01/01              38,718       2006-01-01            38,718  
 2003/05/11              37,752       2003-05-11            37,752  
 2003/05/01              37,742       2003-05-01            37,742  
 2003/04/22              37,733       2003-04-22            37,733  
 2003/04/15              37,726       2003-04-15            37,726  
 2003/04/01              37,712       2003-04-01            37,712  
 2003/03/01              37,681       2003-03-01            37,681  
 2003/02/08              37,660       2003-02-08            37,660  
 2003/01/15              37,636       2003-01-15            37,636  
 2003/01/06              37,627       2003-01-06            37,627  
 2003/01/02              37,623       2003-01-02            37,623  
 2003/01/01              37,622       2003-01-01            37,622  
 2002/12/15              37,605       2002-12-15            37,605  
 2002/12/10              37,600       2002-12-10            37,600

[vtorresviera]

Re: BosaNova Transfer utility - S.O. S.

Wow!

I thought that will be easier....

Thank you very much and nice to talk to you again.

Have a nice Wednesday!

[vtorresviera]

Re: BosaNova Transfer utility - S.O. S.

I am saving the file as CSV text (comma delimited)