News
Abstract
Db2 Connect - Date and Time format controls
Content
You are in: IBM i Technology Updates > Db2 for i - Technology Updates > Db2 for i Functional Enhancements > Db2 Connect - Date and Time format controls
DB2 Connect V10.5 and IBM i 6.1 & 7.1 have been enhanced to provide DATE/TIME/DECIMAL format & separator control by supporting new environment level and connection level attributes.
Attribute names:
SQL_ATTR_DATE_FMT
SQL_ATTR_DATE_SEP
SQL_ATTR_TIME_FMT
SQL_ATTR_TIME_SEP
SQL_ATTR_DECIMAL_SEP
Date format values:
SQL_IBMi_FMT_YMD
SQL_IBMi_FMT_MDY
SQL_IBMi_FMT_DMY
SQL_IBMi_FMT_JUL
SQL_IBMi_FMT_ISO
SQL_IBMi_FMT_EUR
SQL_IBMi_FMT_JIS
SQL_IBMi_FMT_JOB
SQL_IBMi_FMT_USA
SQL_IBMi_FMT_JOB
SQL_IBMi_FMT_USA
Date separator values:
SQL_SEP_SLASH
SQL_SEP_COMMA
SQL_SEP_PERIOD
SQL_SEP_BLANK
SQL_SEP_DASH
SQL_SEP_JOB
Time format values:
SQL_IBMi_FMT_HMS
SQL_IBMi_FMT_ISO
SQL_IBMi_FMT_USA
SQL_IBMi_FMT_EUR
SQL_IBMi_FMT_JIS
Time separator values:
SQL_SEP_COLON
SQL_SEP_PERIOD
SQL_SEP_COMMA
SQL_SEP_BLANK
SQL_SEP_JOB
Decimal separator values:
SQL_SEP_PERIOD
SQL_SEP_COMMA
SQL_SEP_JOB
Programming Notes:
sqlsetenvattr() - called before a connection is made
sqlsetconnectattr() - called before or after a connection is made
DB2 for LUW 10.5 Product Page:
http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/index.jsp?topic=%2Fcom.ibm.db2.luw.wn.doc%2Fdoc%2Fc0060895.html
Example: Find employees with less than 2 years of tenure
sqlsetconnectattr 1 SQL_ATTR_DATE_FMT SQL_IBMi_FMT_EUR
sqlsetconnectattr 1 SQL_ATTR_DATE_SEP SQL_SEP_PERIOD
sqlexecdirect 1 “SELECT EMPNO FROM CORPDATA.EMP
WHERE HIREDATE > ‘09.23.2013‘ – 2 YEARS
ORDER BY LASTNAME, FIRSTNME, MIDINIT" -3
WHERE HIREDATE > ‘09.23.2013‘ – 2 YEARS
ORDER BY LASTNAME, FIRSTNME, MIDINIT" -3
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]
Was this topic helpful?
Document Information
Modified date:
14 January 2020
UID
ibm11167292