CODEWITHSUNDEEP
sqloracle-database
franco
franco

Reputation: 2047

format of date using to_date function

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

Upvotes: 1

Views: 1729

Answers (3)

Noel
Noel

Reputation: 10525

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')

Upvotes: 2

Guillem Vicens
Guillem Vicens

Reputation: 3996

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.

Upvotes: 0

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

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;

Upvotes: 0

Related Questions