LabVIEW

cancel
Showing results for 
Search instead for 
Did you mean: 

query date format db tools to oracle

Hi all

OK I give up. I am trying to create a query from LabView db tools to an Oracle data base and so far it keeps giving me this error no matter how I change the month

 

 

Conn Execute.vi->DB_Get_Test_IDs.vi<ERR>Exception occured in Microsoft OLE DB Provider for ODBC Drivers: [Oracle][ODBC][Ora]ORA-01843: not a valid month
 in Conn Execute.vi->DB_Get_Test_IDs.vi

 

I am hoping anyone out there has used db tools with  a query that has a datetime condition.

 

Here is mine

 

SELECT Di_Device.SER_NUMBER,Di_Device.DEV_ID,Di_Test2.DATE_TIME,Di_Test2.TEST_ID
FROM Di_Device, Di_Test2 WHERE (Di_Device.Dev_Id=Di_Test2.Dev_Id)  AND (Di_Device.Ser_Number=218647 AND Di_Test2.Date_Time BETWEEN '18-07-2009 13:00:00' AND '19-07-2009 02:00:00')

 

Thanks in advance

 

Mario

 

0 Kudos
Message 1 of 3
(2,783 Views)

elanimal wrote:

Hi all

OK I give up. I am trying to create a query from LabView db tools to an Oracle data base and so far it keeps giving me this error no matter how I change the month

 

 

Conn Execute.vi->DB_Get_Test_IDs.vi<ERR>Exception occured in Microsoft OLE DB Provider for ODBC Drivers: [Oracle][ODBC][Ora]ORA-01843: not a valid month
 in Conn Execute.vi->DB_Get_Test_IDs.vi

 

I am hoping anyone out there has used db tools with  a query that has a datetime condition.

 

Here is mine

 

SELECT Di_Device.SER_NUMBER,Di_Device.DEV_ID,Di_Test2.DATE_TIME,Di_Test2.TEST_ID
FROM Di_Device, Di_Test2 WHERE (Di_Device.Dev_Id=Di_Test2.Dev_Id)  AND (Di_Device.Ser_Number=218647 AND Di_Test2.Date_Time BETWEEN '18-07-2009 13:00:00' AND '19-07-2009 02:00:00')

 

Thanks in advance

 

Mario

 


I have no experience with Oracle, but from SQL Server I know it is a bad idea to use date/time strings directly in any query. Instead you should use whatever facility Oracle gives you to convert a particular string into a data/time value. For SQL Server this is the CONVERT(DATETIME, <string>, 121) function that takes as first parameter the type of data one wants to create, as second parameter the string to convert, and as third parameter an identifier for the actual format used (121 is the standardized ODBC data/time format e.g. '2009-08-20 10:30:00.125'). Without CONVERT(), SQL server is supposed to recognize that format anyhow but I had trouble on some computers with a different local date/time format than the default US.

 

Otherwise the SQL Server ODBC driver (and any other MS database driver) will attempt to use whatever local format is used on the client computer and get in a mess when that does not match the date/time format of the computer where the server is installed. Oracle may do things a bit different but probably run into similar problems if you do not take care about making sure the data/time is converted specifically from the format you provide.

 

Rolf Kalbermatter

Message Edited by rolfk on 08-20-2009 10:34 AM
Rolf Kalbermatter
My Blog
Message 2 of 3
(2,701 Views)

Hi Rolf,

 

A small request...

 

Please dont quote the previous post (partial/full) when you are the immediate replier (is this a correct word; earlier I thought of 'answerer') to it. Smiley Happy

 

It spoils my anxiety/curiosity of reading your detailed description, bcos I need to scroll down more, because of your quoted text. Smiley Very Happy

- Partha ( CLD until Oct 2024 🙂 )
0 Kudos
Message 3 of 3
(2,684 Views)