format of date using to_date function

Question

I have a query which use a date I work with oracle

but I have a problem in format of date

when I run my query I have an error

this my query test:

select NUM_REQUEST, 
 from REQUEST_TEST

where REQUEST_DATE between TO_DATE('Thu Nov 07 00:00:00 CET 2013','MM/dd/yyyy hh:mm:ss a')  and TO_DATE('Mon Dec 23 00:00:00 CET 2013','MM/dd/yyyy hh:mm:ss a'))

I have an error in foramt of date using to_date function

when I try with this query and I have a correct result

select NUM_REQUEST, 
 from REQUEST_TEST

where REQUEST_DATE between to_timestamp_tz('Thu Nov 07 00:00:00 CET 2013','Dy Mon dd hh24:mi:ss TZR yyyy')  and to_timestamp_tz('Mon Dec 23 00:00:00 CET 2013','Dy Mon dd hh24:mi:ss TZR yyyy'))

but when I test in my application with :

  String sqlQuery = "select NUM_REQUEST, from REQUEST_TEST" + 

  " where REQUEST_DATE between to_timestamp_tz('Thu Nov 07 00:00:00 CET 2013','Dy Mon dd hh24:mi:ss TZR yyyy')  and to_timestamp_tz('Mon Dec 23 00:00:00 CET 2013','Dy Mon dd  hh24:mi:ss TZR yyyy')) "; 
Query query = this.getSession().createSQLQuery(sqlQuery);

I have this error :

ORA-01846: ce n'est pas un jour de semaine valide
org.hibernate.exception.DataException: could not execute query

I have for example in my column REQUEST_DATE this data 29-NOV-13 and when I edit this column I have this format for example : friday, 29 Novembre 2013 00:00:00 o'clock CET

The string that holds your date (Thu Nov...) is completely different to the date format model you're providing to the same function (MM/DD/...). Not really surprising that it's complaining. You may need to be working with timestamp rather than date if you're passing in a time zone. You seem to have a trailing comma on the first line as well. It's generally useful to show the actual error you get. — Alex Poole
– Alex Poole, Commented Dec 23, 2013 at 9:31
thank you for your response, I want to know the format corresponding to this date Thu Nov 07 00:00:00 CET 2013 — franco
– franco, Commented Dec 23, 2013 at 9:33
Off topic, but please consider to accept and/or upvote answers to your questions if they help you. That would help others to detect if given answers are useful or not. — Guillem Vicens
– Guillem Vicens, Commented Dec 23, 2013 at 10:55

Noel · Accepted Answer · 2013-12-23 11:16:39Z

As others have said, your date format must match your date string. Have a look at Oracle Datetime Format Elements for all possible elements. Also, since there is a timezone in your string you need to cast it to timestamp datatype and not date. So, you need to use TO_TIMESTAMP_TZ function.

So, to answer you question it should be something like this.

to_timestamp_tz('Thu Nov 07 00:00:00 CET 2013','Dy Mon dd hh24:mi:ss TZR yyyy')

Looks like your language setting is french. So you need to supply the month/day accordingly.

to_timestamp_tz('Jeu. Nov. 07 00:00:00 CET 2013','Dy Mon dd hh24:mi:ss TZR yyyy')

or you should explicitly change the language to English, which you can do in the function call itself.

to_timestamp_tz('Thu Nov 07 00:00:00 CET 2013',
                'Dy Mon dd hh24:mi:ss TZR yyyy',
                'nls_date_language = ENGLISH')

+1, I did not see your answer until I posted mine. Will leave it as it has slightly different information but yours is more complete.

Maheswaran Ravisankar · Accepted Answer · 2013-12-23 09:55:23Z

0

Probably you need this. And I believe you get this date format from UNIX. You can also try formatting the date from Unix if so.

select to_timestamp_tz('Thu Nov 07 00:00:00 EDT 2013','Dy Mon DD HH24:MI:SS TZD YYYY') from dual;

answered Dec 23, 2013 at 9:55

Maheswaran Ravisankar

17.9k6 gold badges50 silver badges69 bronze badges

Comments

Guillem Vicens · Accepted Answer · 2013-12-23 10:53:23Z

The closest one I can think of would be:

select NUM_REQUEST, 
 from REQUEST_TEST
 where REQUEST_DATE Between TO_TIMESTAMP_TZ('Thu Nov 07 00:00:00 CET 2013','DY MON DD  HH24:MI:SS TZR YYYY' )  And TO_TIMESTAMP_TZ('Mon Dec 23 00:00:00 CET 2013','DY MON DD  HH24:MI:SS TZR YYYY')

Be careful with the month abbreviations, as they depend on your locale language settings. For example, December is Dec in English but Dic in Spanish.

Update: your problem with ORA-01846 is related as well to your locale language settings. You need to use the abbreviation corresponding to your language. For example, I guess that for French the abbreviation for Friday (Vendredi in French) would be Ven.

Collectives™ on Stack Overflow

format of date using to_date function

3 Answers 3

1 Comment

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Related