Oracle BETWEEN DateTime statement

Question

Oracle BETWEEN DateTime statement

jimtimber

SSC Eights!

Points: 824
More actions
April 1, 2016 at 8:29 am

#309636

Hello,
I've connected to a new database at work via VBA. I've entered SQL script into my VB code and it works fine. However, when I try yo put a WHERE clause on a datetime field, it keeps returning "ambiguous datetime". I've tried many variations of YYYY-MM-DD HH:MM:SS (milisecs etc) and it keeps returning this error.
"[Oracle][ODBC][Rdb]%SQL-F-DATCONERR, Data conversion error for string '01/01/2016' = COSI-F-AMBDATTIM, ambiguous date-time"
Is there a set format for using DateTime fields in a WHERE CLAUSE with Oracle please. My SQL code is:
SELECT UserID, UserAge, DateCreated
FROM UserDetails
WHERE DateCreated BETWEEN '01/01/2016 00:00:00' AND '31/01/2016 00:00:00'
Thank you.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

pretty sure you have to use oracles TO_DATE function, especially because you are passing a string,and also using UK date formats.

WHERE DateCreated BETWEEN

TO_DATE('01/01/2016 00:00:00','dd/mm/yyyy hh:mi:ss')

AND TO_DATE('31/01/2016 00:00:00','dd/mm/yyyy hh:mi:ss')

the problem would disappear if you used parameterized queries, because the query would get the under the hood value for a datetime typed datatype, regardless of visible formatting.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jimtimber SSC Eights! Points: 824 More actions · Answer 2

Hi Lowell,

I have just tried that, it returned an error saying 'function or procedure TO_DATE is not defined'.

Lowell SSC Guru Points: 323504 More actions · Answer 3

you posted in "working with oracle", so i thought you were using oracle.

if this is SQL server, then you would just stick with dateformats that are universally converted;

you said you tried YYYY-MM-DD HH:MM:SS like '2016-04-01 11:19:29', right? same format that SELECT CONVERT(VARCHAR,GETDATE(),120) returns?

do you get an ambiguous error with that?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jimtimber SSC Eights! Points: 824 More actions · Answer 4

I am using VB in Excel to connect to a database used by Business Objects. BO is slow so I am using VB to bypass BO. for example, one report I run in BO takes 15 mins, in my VB version it takes about 5 secs.

Its a new DB which I know nothing about. The only way I know it is using Oracle somewhere is because the errors being returned in VB all start with "Oracle...".

I've tried using T-SQL I use in other reports for this report but that calls from a different DB and doesn't work when calling from this DB.

EDIT: I tried the (VARCHAR,GETDATE(),120) part, it returns error "[oracle][odbc][tdb]%SQL F-SYNTAX_ERR, Syntax error"

jimtimber SSC Eights! Points: 824 More actions · Answer 5

I've found that it seems to accept the date format "01 JANUARY 2016" but no other format. Not sure why this is a default date type.

Ed Wagner SSC Guru Points: 287024 More actions · Answer 6

jimtimber (4/2/2016)
I've found that it seems to accept the date format "01 JANUARY 2016" but no other format. Not sure why this is a default date type.

In Oracle, you can set your dateformat for the session. What Lowell posted with TO_DATE is correct.

However, I'd suggest opening SQL*Plus and writing your query there to get it working. Once you know you have a working query, introduce other variables into the equation like the Oracle ODBC driver.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions