Data Manipulation

[programmer]

I want to find out the records in a field with date format dd/mm/yyyy hh:mi:ss AM

[jmodic]

I want to find out the records in a field with date format dd/mm/yyyy hh:mi:ss AM

And your "field" is of type VARCHAR2, no?

It is not, by any chance, of type DATE?

[programmer]

The field is of type DATE

[davey23uk]

so whats the problem?

[programmer]

I want a Sql query to select and find all records in the format dd/mm/yyyy hh:mi:ss AM in the database. The field name is EFF_END_DT which is date field.
There are some records with the date format 'dd/mm/yyyy' and some with the format 'dd/mm/yyyy hh:mi:ss am'

[davey23uk]

no, the format is how you ask it to be displayed, a date is a date is a date, they are all stored the same

[gamyers]

If you are asking which rows have a non-midnight time component, then
EFF_END_DT != TRUNC(EFF_END_DT)
is probably best.