I have a little problem with a select command that is suposed to select *

from table where date < 'some date'



I'm using Oracle8i and IIS5=2E



Now the problem is that the "date" col has the type set to "Date"=2E When 

I

insert a new row, oracle formats the date like this:"1/26/2001 9:26:39 AM"

=2E

How the he=2E=2E do I select a < or > value from this table using the "dat

e" as

a search variable?



As you can see I'm new to oracle and sql, but if anyone can help me it

would be great=2E



p=2Es=2E This is'nt the only schema so I cant chance any date formats in o

racle

it self, I have to do it on the asp page=2E

You can use "cast" to cast the date to a short string and then cast it back 

to a date



Select cast(cast(aDateColumn as varchar(12)) as datetime) from yourTable 

where cast(cast(aDateColumn as varchar(12)) as datetime) >= '2001-01-12'





This will select all records with a date that is equal to or larger than 

January 12 2001



Basically what happens is that the time portion of the dateTime is reset to 

00:00:00.000 so you can compare other dates with the same time portion with it.



HtH



Imar







At 09:50 AM 1/16/2001 +0000, you wrote:

>I have a little problem with a select command that is suposed to select *

>from table where date < 'some date'

>

>I'm using Oracle8i and IIS5.

>

>Now the problem is that the "date" col has the type set to "Date". When I

>insert a new row, oracle formats the date like this:"1/26/2001 9:26:39 AM".

>How the he.. do I select a < or > value from this table using the "date" as

>a search variable?

>

>As you can see I'm new to oracle and sql, but if anyone can help me it

>would be great.

>

>p.s. This is'nt the only schema so I cant chance any date formats in oracle

>it self, I have to do it on the asp page.

 Hi,



When you have a select write it like :

select to_char(yourdatefield,'DD/MM/YYYY') as thedate 

from uourtable



When inserting them into the database using stored procedures,

pass it as a string, and insert it like :

insert into yourtable values to_date(yourdatefield, 'DD/MM/YYYY')



Greetings,



Koen Van Tolhuyzen





-----Original Message-----

From: bjarni@v...

To: ASP Databases

Sent: 16/01/01 10:50

Subject: [asp_databases] select  * from table where date......



I have a little problem with a select command that is suposed to select

*

from table where date < 'some date'



I'm using Oracle8i and IIS5.



Now the problem is that the "date" col has the type set to "Date". When

I

insert a new row, oracle formats the date like this:"1/26/2001 9:26:39

AM".

How the he.. do I select a < or > value from this table using the "date"

as

a search variable?



As you can see I'm new to oracle and sql, but if anyone can help me it

would be great.



p.s. This is'nt the only schema so I cant chance any date formats in

oracle

it self, I have to do it on the asp page.