date trouble

[SvenSiwek]

Hi!

First: please be lenient with my English... thank you

In the preferences I have put the date format to user defined DD.MM.YYYY .

I do have a table with a date field.

When I try to do the following:

select * from table where table_datefield = '31.12.2004'

I do get an ORA-01843.

I have tried some variations with the date but it won

 

[LMW]

Hello,

if there is fractional part (time) in the date field , you have to select with trunc(table_datefield) = '31.12.2004'

The preference-setting is for formatting the output not for querying.

regards
Wilhelm

 

[SvenSiwek]

Originally posted by LMW:
Hello,

if there is fractional part (time) in the date field , you have to select with trunc(table_datefield) = '31.12.2004'

The preference-setting is for formatting the output not for querying.

regards
Wilhelm

Thank you for your quick response.
The problem is on the right side of the equals sign.

 

[TenGor]

Hy,

to be shure that the left side is equal to the rigth i would appologize:

select * from table where table_datefield = to_date('31.12.2004', 'DD.MM.YYYY');

So you have a date field on right too.

 

[BWendling]

The standard Oracle format is dd-Mon-yyyy, e.g. 04-FEB-2005

You can define "your" Oracle Date format by setting the user/system variable
NLS_DATE_FORMAT=DD.MM.YYYY

 

[SvenSiwek]

Originally posted by BWendling:
The standard Oracle format is dd-Mon-yyyy, e.g. 04-FEB-2005

You can define "your" Oracle Date format by setting the user/system variable
NLS_DATE_FORMAT=DD.MM.YYYY

Thank you!

That works.

Have a nice weekend