Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Simple Oracle SQL date syntax 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?

like image 572
Joe Avatar asked Nov 18 '08 19:11

Joe


3 Answers

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

like image 108
Bill Karwin Avatar answered Sep 21 '22 10:09

Bill Karwin


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.

like image 30
gpojd Avatar answered Sep 23 '22 10:09

gpojd


according to this you can use the following:

to_date('19960725','YYYYMMDD')
like image 34
Vincent Ramdhanie Avatar answered Sep 21 '22 10:09

Vincent Ramdhanie