0

I'm provided with Composite Software driver using which I've created a ODBC connection and fetching data. I'm not able to find the syntax as how to format the datetime. I tried this - 

select MY_DATE from Master_Table

I get the value as 9/3/2013 12:00:00 AM. I want to format it as 9/3/2013 without time and I need to concat it with another column.

I tried the following query based on this manual - 

SELECT CONVERT(nvarchar(30), MY_DATE, 103) AS Expr1 FROM Master_Table

And I get the error -

SQL Execution Error.

Executed SQL statement: SELECT CONVERT (nvarchar(30), MY_DATE, 103) AS Expr1 FROM Master_Table Error Source: Composite Software 6.1.0 ODBC Driver Error Message: ERROR [HY000] Unable to parse query text: Incorrect syntax near "SELECT", found "CONVERT". On line 1, column 8. [parser-2904201] Cause: Incorrect syntax near "SELECT", found "CONVERT"

The problem is I have no idea how to find sql syntax for Composite Information Server. Or will the SQL syntax will be same for all ODBC connections? Any idea how to fix this?

NLV
  • 21,141
  • 40
  • 118
  • 183
  • 1
    The ODBC driver doesn't appear to support the syntax - you really need to manual for the database driver to determine what you can do. An alternative is to pull the data across to a MSSQL server and query it there (you could add it as a linked server using the same driver) – Charleh May 14 '14 at 10:31
  • See if there's any more info in the properties of the driver you used to set up the ODBC DSN; ask the DB admin; try lots of simple queries using vendor specific SQL features to work out what DB is being used. PostgreSQL has a `::` CASTing shortcut which may be unique to that DB, for example. Here's an 'Oracle Detector' http://stackoverflow.com/questions/101184/how-can-i-confirm-a-database-is-oracle-what-version-it-is-using-sql – AjV Jsy May 14 '14 at 11:15
  • I tried TO_CHAR(MY_DATE, 'MM/DD/YYYY') and it is working. Seems to work with PL-SQL sytax. – NLV May 14 '14 at 11:20

0 Answers0