Postgres Pro
Downloads
Home   >   mailing lists

Thread: date format problem

date format problem

From
Bill Barnes
Date:
Hello List:

Using 6.5.3 on SuSE 6.4.

Built a table with a date field as type 'datetime'.

I try to copy a text file to the table and get a 'bad date format'
Dates in the text file may be null, but are in the form 1997-12-15.
Have tried several of the date field types with the same result.
The copy executes properly if I redefine the date field to varchar.

Thanks for your help.
Bill Barnes

------------------------------------------------------------
This e-mail has been sent to  you  courtesy of OperaMail,  a
free  web-based  service  from  Opera  Software,  makers  of
the award-winning Web Browser - http://www.operasoftware.com
------------------------------------------------------------

Re: date format problem

From
"Anand Raman"
Date:
HI bill
Try to cast the field to the datetime type

'ur_string_goes_here'::datetime

Hope this will help
Anand Raman

----- Original Message -----
From: Bill Barnes <bbarnes@operamail.com>
To: <pgsql-general@postgresql.org>
Sent: Friday, April 28, 2000 6:09 PM
Subject: [GENERAL] date format problem


> Hello List:
>
> Using 6.5.3 on SuSE 6.4.
>
> Built a table with a date field as type 'datetime'.
>
> I try to copy a text file to the table and get a 'bad date format'
> Dates in the text file may be null, but are in the form 1997-12-15.
> Have tried several of the date field types with the same result.
> The copy executes properly if I redefine the date field to varchar.
>
> Thanks for your help.
> Bill Barnes
>
> ------------------------------------------------------------
> This e-mail has been sent to  you  courtesy of OperaMail,  a
> free  web-based  service  from  Opera  Software,  makers  of
> the award-winning Web Browser - http://www.operasoftware.com
> ------------------------------------------------------------
>

RE: date format problem

From
Bill Barnes
Date:
Hello Anand:

Thanks for the input.

The problem turned out to be date fields with null values.  Disappointing!
A lot of my Sybase procedures tested for null dates.

So my workaround was to edit the null dates to a pseudo date.  This is least
got my database poplulated.  Will try the cast for queries and updates.

I don't know which SQL, Postresql or Sybase, is an extension or non-compliant.

Bill Barnes

>===== Original Message From "Anand Raman" <araman@india-today.com> =====
>HI bill
>Try to cast the field to the datetime type
>
>'ur_string_goes_here'::datetime
>
>Hope this will help
>Anand Raman
>
>----- Original Message -----
>From: Bill Barnes <bbarnes@operamail.com>
>To: <pgsql-general@postgresql.org>
>Sent: Friday, April 28, 2000 6:09 PM
>Subject: [GENERAL] date format problem
>
>
>> Hello List:
>>
>> Using 6.5.3 on SuSE 6.4.
>>
>> Built a table with a date field as type 'datetime'.
>>
>> I try to copy a text file to the table and get a 'bad date format'
>> Dates in the text file may be null, but are in the form 1997-12-15.
>> Have tried several of the date field types with the same result.
>> The copy executes properly if I redefine the date field to varchar.
>>
>> Thanks for your help.
>> Bill Barnes
>>
>> ------------------------------------------------------------
>> This e-mail has been sent to  you  courtesy of OperaMail,  a
>> free  web-based  service  from  Opera  Software,  makers  of
>> the award-winning Web Browser - http://www.operasoftware.com
>> ------------------------------------------------------------
>>

------------------------------------------------------------
This e-mail has been sent to  you  courtesy of OperaMail,  a
free  web-based  service  from  Opera  Software,  makers  of
the award-winning Web Browser - http://www.operasoftware.com
------------------------------------------------------------

RE: date format problem

From
Lincoln Yeoh
Date:
I dunno, I'm using 6.5.3 and have no probs pgdumping, pguploading and using
null dates - moved my app to another server etc, and the null dates are
still there. The null dates are dumped as \N which presumably means NULL
(but for some reason typing \N  (two characters - backslash then N) works
for the postgres superuser default password).

So perhaps you could convert dates you want null to \N.

I'm using perl and DBI, and things work fine - a null date is returned as a
perl undefined value (which is different from zero).
e.g.
if not defined($date) {
    $date="<i>unknown</i>";
} else {
    $date=htdate($date);
}

Cheerio,

Link.

At 04:21 AM 03-05-2000 -0400, Bill Barnes wrote:
>Hello Anand:
>
>Thanks for the input.
>
>The problem turned out to be date fields with null values.  Disappointing!
>A lot of my Sybase procedures tested for null dates.
>
>So my workaround was to edit the null dates to a pseudo date.  This is least
>got my database poplulated.  Will try the cast for queries and updates.
>
>I don't know which SQL, Postresql or Sybase, is an extension or
non-compliant.
>
>Bill Barnes
>
>>===== Original Message From "Anand Raman" <araman@india-today.com> =====
>>HI bill
>>Try to cast the field to the datetime type
>>
>>'ur_string_goes_here'::datetime
>>
>>Hope this will help
>>Anand Raman
>>
>>----- Original Message -----
>>From: Bill Barnes <bbarnes@operamail.com>
>>To: <pgsql-general@postgresql.org>
>>Sent: Friday, April 28, 2000 6:09 PM
>>Subject: [GENERAL] date format problem
>>
>>
>>> Hello List:
>>>
>>> Using 6.5.3 on SuSE 6.4.
>>>
>>> Built a table with a date field as type 'datetime'.
>>>
>>> I try to copy a text file to the table and get a 'bad date format'
>>> Dates in the text file may be null, but are in the form 1997-12-15.
>>> Have tried several of the date field types with the same result.
>>> The copy executes properly if I redefine the date field to varchar.
>>>
>>> Thanks for your help.
>>> Bill Barnes
>>>
>>> ------------------------------------------------------------
>>> This e-mail has been sent to  you  courtesy of OperaMail,  a
>>> free  web-based  service  from  Opera  Software,  makers  of
>>> the award-winning Web Browser - http://www.operasoftware.com
>>> ------------------------------------------------------------
>>>
>
>------------------------------------------------------------
>This e-mail has been sent to  you  courtesy of OperaMail,  a
>free  web-based  service  from  Opera  Software,  makers  of
>the award-winning Web Browser - http://www.operasoftware.com
>------------------------------------------------------------
>
>
>

Re: date format problem

From
"Anand Raman"
Date:
hi bill
Null fields shouldnt be the cause of the problem..

A null text field gets translated to a null datetime..

The problem must be because ur database might be specifying the field to be NOT NULL.. Check it out
.. If thats the cause there is no way but to drop that constraint..

Hope this helps
Anand Raman
----- Original Message -----
From: Bill Barnes <bbarnes@operamail.com>
To: Anand Raman <araman@india-today.com>; <pgsql-general@postgresql.org>
Sent: Wednesday, May 03, 2000 1:51 PM
Subject: RE: [GENERAL] date format problem


> Hello Anand:
>
> Thanks for the input.
>
> The problem turned out to be date fields with null values.  Disappointing!
> A lot of my Sybase procedures tested for null dates.
>
> So my workaround was to edit the null dates to a pseudo date.  This is least
> got my database poplulated.  Will try the cast for queries and updates.
>
> I don't know which SQL, Postresql or Sybase, is an extension or non-compliant.
>
> Bill Barnes
>
> >===== Original Message From "Anand Raman" <araman@india-today.com> =====
> >HI bill
> >Try to cast the field to the datetime type
> >
> >'ur_string_goes_here'::datetime
> >
> >Hope this will help
> >Anand Raman
> >
> >----- Original Message -----
> >From: Bill Barnes <bbarnes@operamail.com>
> >To: <pgsql-general@postgresql.org>
> >Sent: Friday, April 28, 2000 6:09 PM
> >Subject: [GENERAL] date format problem
> >
> >
> >> Hello List:
> >>
> >> Using 6.5.3 on SuSE 6.4.
> >>
> >> Built a table with a date field as type 'datetime'.
> >>
> >> I try to copy a text file to the table and get a 'bad date format'
> >> Dates in the text file may be null, but are in the form 1997-12-15.
> >> Have tried several of the date field types with the same result.
> >> The copy executes properly if I redefine the date field to varchar.
> >>
> >> Thanks for your help.
> >> Bill Barnes
> >>
> >> ------------------------------------------------------------
> >> This e-mail has been sent to  you  courtesy of OperaMail,  a
> >> free  web-based  service  from  Opera  Software,  makers  of
> >> the award-winning Web Browser - http://www.operasoftware.com
> >> ------------------------------------------------------------
> >>
>
> ------------------------------------------------------------
> This e-mail has been sent to  you  courtesy of OperaMail,  a
> free  web-based  service  from  Opera  Software,  makers  of
> the award-winning Web Browser - http://www.operasoftware.com
> ------------------------------------------------------------
>