Thread: HOW TO HANDLE ZEROS IN DATE FIELD?

I have a project that is taking input from another system. I have certain
columns defined as 'Date'

Columns. On input I will get '000000' in this field which causes the insert
to fail. I have read the

docs on default and it is unclear to me if this will work. Does anyone have
experience in solving

this problem. The other alternative I thought of was to write a trigger to
fix it ??



James M Doherty
jim@jdoherty.net
Georgetown, TX 78626
"There is no luck without discipline" IRISH PROVERB

On Sep 3, 2004, at 11:36 PM, James M Doherty wrote:

> I have a project that is taking input from another system. I have 
> certain
> columns defined as 'Date'
>
> Columns. On input I will get '000000' in this field which causes the 
> insert
> to fail. I have read the
>
> docs on default and it is unclear to me if this will work. Does anyone 
> have
> experience in solving
>
> this problem. The other alternative I thought of was to write a 
> trigger to
> fix it ??

000000 is not a valid date, as you are aware :) This is often solved in 
the application layer by preprocessing the data, changing 000000 to 
NULL, for example. You may be able to do some of this preprocessing in 
the database itself, first loading the raw data into a temporary table 
and then transforming it before putting it into the desired table.

Michael Glaesemann
grzm myrealbox com

> On Sep 3, 2004, at 11:36 PM, James M Doherty wrote:
>
>> I have a project that is taking input from another system. I have
>> certain columns defined as 'Date' Columns. On input I will get 
>> '000000' in this field which causes the insert to fail.


> 000000 is not a valid date, as you are aware :) This is often solved in
> the application layer by preprocessing the data, changing 000000 to
> NULL, for example. You may be able to do some of this preprocessing in
> the database itself, first loading the raw data into a temporary table
> and then transforming it before putting it into the desired table.
>

I've CC'd the list as well.

On Sep 11, 2004, at 9:44 PM, James M Doherty @ jdoherty.net wrote:

>     I was hoping I could do something in my insert trigger to change it 
> on the
> way in and avoid code in client applications. The reason for this is 
> there
> are
> many client application which will be doing these inserts.

In that case I would recommend either using temp tables to process the 
data, or build a middleware app that other client apps would connect 
to, rather than to the db directly, the goal being to have a single API 
against which you can code the client apps.

Good luck!

Michael Glaesemann
grzm myrealbox com