April 1, 2016 at 8:29 am
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.
April 1, 2016 at 8:35 am
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
April 1, 2016 at 8:52 am
Hi Lowell,
I have just tried that, it returned an error saying 'function or procedure TO_DATE is not defined'.
April 1, 2016 at 9:21 am
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
April 1, 2016 at 9:39 am
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"
April 2, 2016 at 1:26 pm
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.
April 3, 2016 at 1:20 am
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.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy