Question posted 2008 · +6 upvotes
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?
Accepted answer +14 upvotes
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
2 code variants in this answer
- Variant 1 — 1 lines, starts with
DATE '2008-11-11' - Variant 2 — 1 lines, starts with
TO_DATE('11/11/2008', 'MM/DD/YYYY')
Top ms-access Q&A (6)
- How can I modify a saved Microsoft Access 2007 or 2010 Import Specification? +31 (2008)
- OleDbCommand parameters order and priority +28 (2009)
- Is there an equivalent to the SUBSTRING function in MS Access SQL? +26 (2009)
- What do I need to read Microsoft Access databases using Python? +25 (2009)
- MS Access library for python +24 (2009)
- is there any replacement of Access? +21 (2009)
ms-access solutions on this site
— top 20%.