OraFAQ Forum: SQL & PL/SQL » Conversion from char to date and compare

Home » SQL & PL/SQL » SQL & PL/SQL » Conversion from char to date and compare

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Conversion from char to date and compare [message #20993]

Fri, 05 July 2002 02:48

Dev
Messages: 28
Registered: May 2001

Junior Member

Hi All,

I have a table with field as date_created (char)
which is as follows :

Wed Mar 07 06:35:09 EST 2001

I want to know the count of all the records where date_created is between some specific period.

I am firing query to get the count from 10.30 to 11.30 on 4th July as follows :

select count(*) from tsl_registered_details
where
to_date((lpad(substr(date_created,9,2),2,0)||'-'||substr(date_created,5,3)||substr(date_created,27,2)||substr(date_created,11,9)),'dd-mon-yy hh24:mi:ss') >to_date('04-JUL-02 10:29:00','dd-mon-yy hh24:mi:ss')
and
to_date((lpad(substr(date_created,9,2),2,0)||'-'||substr(date_created,5,3)||substr(date_created,27,2)||substr(date_created,11,9)),'dd-mon-yy hh24:mi:ss') < to_date('04-JUL-02 11:31:00','dd-mon-yy hh24:mi:ss')

But it is giving me the error as literal does not match format string.

How can I run a query ot get the same.

Regards,

Report message to a moderator

Re: Conversion from char to date and compare [message #21001 is a reply to message #20993]

Fri, 05 July 2002 06:54

Arn
Messages: 2
Registered: July 2002

Junior Member

Try this

select count(*) from tsl_registered_details
where
to_date((lpad(substr(date_created,9,2),2,0)||'-'||substr(date_created,5,3)||
substr(date_created,27,2)||substr(date_created,11,9)),'dd-mon-yy hh24:mi:ss')
between to_date('04-JUL-02 10:29:00','dd-mon-yy hh24:mi:ss') and
to_date('04-JUL-02 11:31:00','dd-mon-yy hh24:mi:ss')

Report message to a moderator

Previous Topic:	how can I get the full date data?
Next Topic:	Re: Date formats

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 09:56:56 CDT 2024