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.