datetime format in a SQL query

[hlauwers]

Hello all!

I'm attempting to retrieve the records from table X where DAT is greater than 31/12/2004 9am

The WHERE clauses I've tried so far:

WHERE dat > '31/12/2004 9:00:00'
WHERE dat > '31/12/2004 9:00:00 AM'
WHERE dat > '31/12/2004 9:00:00 AM CET'

all return the error ORA-01830: date format picture ends before converting entire input string

What is the correct format to represent Oracle's datetime in SQL?

 

[KenCunningham]

How is dat presented in a simple select statement?

 

[sem]

You need explicit data conversion:

WHERE dat > to_date('31/12/2004 9am','dd/mm/yyyy hham')



Regards, Dima

http://seminihin.narod.ru

 

[hlauwers]

dat in a query result is in format "2003-31-12 06:00:00"


I used the to_date() function, which worked...
but what character is reserved for minutes?

i.e. 'dd/mm/yyyy hh:??:ss am'

thanks

 

[KenCunningham]

MI I think.

 

[carp]

Yes - MI. MM was already taken for indicating months.