SQL queries on date fields
We are using a linked server to query some Cobol data files through AcuXDBC on a new server.
A query looked like this on the old server: select * into _t1 from openquery(XDBC_TESTREM,'select * from gjen'). It was working well. However, after we switched to the new server I started getting some errors and I had to format the date columns like this:
CONVERT(NVL(CONVERT(GJ_DEPOTDATO,SQL_CHAR),TO_DATE(719527,''YYYYMMDD'')),SQL_DATE) AS GJ_DEPOTDATO
I have 3 questions:
1. What can I do to avoid using that convert statement? Is there any setting somewhere that can make this work by default? (otherwise I will have to change all the queries)
2. Some dates are like this: 5112012. They are missing a leading zero. I tried a CASE WHEN char_length(...) = 7 THEN.. but I get an error saying that function 30 is not implemented yet. How can I handle this type of dates? (i am thinking to return them as SQL_CHAR and convert them in t-sql to dates)
3. Where can I find a full list of supported SQL syntax?
RE: SQL queries on date fields
1) The XFD DATE directive should help. $XFD DATE=date-format-string
2) Set invalid_numeric_data zero in your AcuXDBC config file
3) We support the 1992 ANSI standard SQL Syntax