Simple Oracle SQL date syntax question

Question

I am trying to convert a working MS Access query to run on an Oracle database being accessed via VB Script (.asp). This is the last section of the WHERE clause:

sql = sql & "WHERE (UAT.HB.MB_MODE = 'A' AND   UAT.HB.PRINT_DATE >= '" 
& SD & "' AND UAT.HB.PRINT_DATE <= '" & ED  &"'  )"

The variable "SD" (i.e. "start date") is a text string that can contain a value such as "11/11/2008". The same goes for the variable "ED" (i.e. "end date").

However, the dates do not work. Does Oracle require a special way to use dates?

Do the dates have to be converted? Do I surround them with the '#' keyword like you would in MS Access?

Answer 1

The default date format for Oracle is "dd-mon-yy". You can do a TO_CHAR function on the date field to convert it to a format you prefer to match on.

Answer 2

In Oracle, your date should be written as an ANSI date literal like this:

DATE '2008-11-11'

Or converted to a date from a string like this:

TO_DATE('11/11/2008', 'MM/DD/YYYY')

See http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements003.htm#BABGIGCJ

Answer 3

according to this you can use the following:

to_date('19960725','YYYYMMDD')

Answer 4

Don't assume the default Oracle date format is anything. Check the NLS_DATE_FORMAT or use TO_DATE to convert it. Like this:

TO_DATE('2008-11-18 14:13:59', 'YYYY-MM-DD HH24:Mi:SS')

Note the 'Mi' for the minutes and not 'MM'. That catches a lot of people.

Answer 5

Here's a couple examples for converting to and from dates:

• select to_date('2008/11/18:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam') from dual
• select to_char(sysdate, 'mm/dd/yyyy') from dual

Answer 6

The default Oracle date format can change from one database to the next. So if you don't know the date format your database is using then you should use the TO_DATE function with an explicit date format string. Given the fact that the default date format could change at any time, using an explicit date format string is the best thing to do anyway.

ex: TO_DATE('11/11/2008 17:30','MM/DD/YYYY HH24:MI')

Answer 7

Don't forget that the date includes a time (defaults to 00:00:00), so
don't get caught out by something like this:
given three dates, start_date, print_date, end_date, all on the same day
print_date >= start_date AND print_date <= end_date fails because print_date was greater than end_date :
start_date was 2008-11-19 (00:00:00)
and end_date was 2008-11-19 (00:00:00)
and print_date was 2008-11-19 (16:00:00)