CODEWITHSUNDEEP
mysqlsqltoad
vishal gupta
vishal gupta

Reputation: 55

I want to fetch the date timestamp in Toad using SQL Query

I have a column in toad

CREATE_DATE='10/9/2020 2:05:10 AM'

I am trying to fetch this using the below SQL Query but it is giving me an error ''Not a valid Month'.

SELECT * FROM TABLE
WHERE
CREATE_DATE = '10/9/2020 2:05:10 AM'

can someone please help, how can i fetch this column

'

Upvotes: 0

Views: 2926

Answers (1)

Littlefoot
Littlefoot

Reputation: 142798

That looks like a DATE datatype column. If that's so, here's an example:

SQL> create table test (create_date date);

Table created.

SQL> insert into test values (sysdate);

1 row created.

Just to see what's in there:

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> select * From test;

CREATE_DATE
-------------------
09.10.2020 09:15:12

OK; now, use a query which utilizes the TO_DATE function (as we'd want to provide a string to it) with appropriate format mask (so that Oracle knows what is what in that string; whether 09 represents a month, or a day).

For example:

SQL> select * from test
  2  where create_date = to_date('09.10.2020 09:15:12', 'dd.mm.yyyy hh24:mi:ss');

CREATE_DATE
-------------------
09.10.2020 09:15:12

Or, another date format (the one you use):

SQL> select * from test
  2  where create_date = to_date('10/9/2020 9:15:12 am', 'mm/dd/yyyy hh:mi:ss am');

CREATE_DATE
-------------------
09.10.2020 09:15:12

Basically, the keyword is to let the database know what you exactly want, not let it guess. Sometimes it'll succeed, but sometimes it'll fail - just like in your query.

Upvotes: 1

Related Questions