Welcome Serena Central users! CLICK HERE
The migration of the Serena Central community is currently underway. Be sure to read THIS MESSAGE to get your new login set up to access your account.
AlexP1 Absent Member.
Absent Member.
1284 views

SQL queries on date fields

Hi,

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?

Tags (2)
0 Likes
1 Reply
Micro Focus Expert
Micro Focus Expert

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

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.