7

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?

Improve this question

7 Answers 7

Reset to default
17

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 here

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

You need to be careful when doing a <= on a date like this. You will want to add one.
8

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.

Improve this answer

1 Comment

I assume you meant MI for minutes not MM (which would be for the month).
1

according to this you can use the following:

to_date('19960725','YYYYMMDD')
Improve this answer

Comments

0

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.

Improve this answer

3 Comments

The NLS_DATE_FORMAT, which controls the default format for implicit string to date and date to string casts depends on the client locale. When you have European a client PC, the default format changes. Since the client preference overrides the database setting, code may start failing.
Never rely on defaults, though.
...however, knowing the "default default" is useful.
0

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
Improve this answer

Comments

0

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

Improve this answer

Comments

0

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)

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.