Simple Oracle SQL date syntax question
Solution 1
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
Solution 2
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.
Solution 3
according to this you can use the following:
to_date('19960725','YYYYMMDD')
Df.fpm
Updated on November 16, 2020Comments
-
Df.fpm over 3 years
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?
-
Justin Cave over 15 yearsThe 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.
-
Leigh Riffel over 15 yearsI assume you meant MI for minutes not MM (which would be for the month).
-
E_the_Shy over 15 yearsYou need to be careful when doing a <= on a date like this. You will want to add one.
-
David Aldridge over 15 yearsNever rely on defaults, though.
-
Dan Pritts over 5 years...however, knowing the "default default" is useful.