Simple Oracle SQL Date Syntax Question

Question

I am trying to convert a working MS Access query to work in an Oracle database, accessed through 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 may contain a value, such as "11/11/2008." The same applies to the variable "ED" (that is, the "end date").

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

Do I need to convert dates? I surround them with the keyword "#", as in MS Access?

Answer 1

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

 DATE '2008-11-11' 

Or converted to a date from a string like this:

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

Look here

Answer 2

Do not assume that the default Oracle date format is anything. Check 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') 

Pay attention to "Mi" for minutes, not "MM". It catches a lot of people.

Answer 3

according to this you can use the following:

 to_date('19960725','YYYYMMDD') 

Answer 4

Here are some examples to convert 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 5

Oracle's default date format can vary from one database to another. Therefore, if you do not know the date format used by your database, you should use the TO_DATE function with an explicit date format string. Given the fact that the default date format can change at any time, using an explicit date format string is all you can do anyway.

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

Answer 6

Do not forget that the date includes time (by default - 00:00:00), so do not catch 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 larger than end_date :
start_date - 2008-11-19 (00:00:00)
and end_date - 2008-11-19 (00:00:00)
and print_date - 2008-11-19 ( 16:00:00 )

Answer 7

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