Re: Excel data transfer add-in DATE fields

<imho>If you can't trust  the source to give you a valid date 100% of the
time,
(and 07/26/1920 really is a valid date) then I would not put the process
at risk by trying to update directly to a date data type field.
jim

----- Original Message -----
From: "Mail List" <maillist@xxxxxxxxxxxxxxx>
To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
Sent: Monday, June 23, 2003 7:18 PM
Subject: Re: Excel data transfer add-in DATE fields


> Hi Elvis,
>
> In this case, the number of records selected turned out to be only 61
> records, and the first invalid date it found was 07/26/1920, the next was
> 02/15/1928, so testing the month & day fields wouldn't have helped.  These
> were resolved by changing the date format to *USA standard.  It would be
> nice if the Excel add-in showed the data mapping errors so it wouldn't be
> necessary to track down the related server job and look at its joblog.
>
> If this weren't a one-time download, I think a longer term solution (at
> least with this set of data) would be to write an SQL function that
returns
> either a valid date or a special value like 12/31/9999 to indicate an
> invalid date so the download isn't abnormally terminated.
>
> Thanks for your help!
>
> Peter Dow
> Dow Software Services, Inc.
> 909 793-9050 voice
> 909 793-4480 fax
> 909 522-3214 cell
>
> From: "Elvis Budimlic"
> > Peter, can you give examples of the invalid data?
> >
> > If there is a discernable pattern in the invalid date values, I'd
> > recommend a WHERE clause to screen those out.
> > For example, if MONTH column has a bad month, say 15.  You can screen
> > these types of errors by appending this to your original statement:
> > WHERE MONTH > 0 AND MONTH < 13
> > or using alternative syntax
> > WHERE MONTH BETWEEN 1 AND 12.
> > Same would go for day and year columns.
>
>
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>