Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL Date query problem is causing error with to_date.

Re: SQL Date query problem is causing error with to_date.

From: Kevin Crosbie <caoimhinocrosbai_at_yahoo.com>
Date: Wed, 13 Apr 2005 19:15:14 +0200
Message-ID: <1113412535.96f33cdb7e63900c38fb50b9e5c04ec5@teranews>


If Per_Birth_Date is a varchar then you can do:

WHERE to_date(Per_Birth_Date, 'format_string') >= to_date('01-APR-1980', 'DD-MON-YYYY'); where you'll need to find out the format string used in the per_birth_date field:

select Per_Birth_Date
 from Oss_Persons
where rownum < 10;

Make sure that the dates all have the same format and it was not a free-text-field.

"Kal" <kalgill_at_talk21.com> wrote in message news:ec0f1d86.0504130858.353a07af_at_posting.google.com...
> Hi All,
>
> I have an SQL problem with a date field, I am trying to pull Per_Id
> and Per_Birth_Date (Person Id and Person Birth Date), I am using the
> sql below:
>
> SELECT Per_Id, Per_Birth_Date
> FROM Oss_Persons
> WHERE Per_Birth_Date >= to_date('01-APR-1980', 'DD-MON-YYYY');
>
> I recieve the error:
>
> ORA-01830: date format picture ends before converting entire input
> string
>
> However if I used the to_date function on other date fields it works
> correctly, it's only on Per_Birth_Date there is a problem.
>
> I am working on a client site so cannot alter the structure of the DB
> or how the date is stored.
>
> Just doing a select of the Per_Birth_Date produces the date in format
> for example purposes:
>
> 01-APR-1976
> 05-APR-2002
>
> The oracle environment I am using is:
> Oracle7 Server Release 7.3.3.0.0 - Production Release
>
> I think maybe possibly the date is stored as a varchar. I have tried
> everything I can think of manipulating the date converting it using
> to_char with to_date.
>
> Can someone provide some assistance please?
>
> Kind Regards,
>
> Kal
Received on Wed Apr 13 2005 - 12:15:14 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US