It is known that, in Axapta, date data type only contains values of day, month and year whereas in SQL Server, datetime data type contains values of second, minutes and hour as well. So if you want to retrieve values from SQL Server using ODBCConnection, and you need to specify the condition on some date type comlumn, how can you get it done?
Here comes the code to show you how to convert Axapta’s date data type value to datetime value. It is an example to get a voucher value from LedgerTrans table in a given date.
Here comes the code to show you how to convert Axapta’s date data type value to datetime value. It is an example to get a voucher value from LedgerTrans table in a given date.
static void TestDateTimeConversion(Args _args)
{
LoginProperty loginProperty = new LoginProperty();
ODBCConnection con;
Statement stmt;
str sqlString, result;
ResultSet resultSet;
;loginProperty.setServer(‘LocalServer’);
loginProperty.setDatabase(‘AXDB’);
loginProperty.setUsername(‘bmssa’);
loginProperty.setPassword(‘bmssa_pwd’);
con = new ODBCConnection(loginProperty);sqlString = "SELECT * FROM LedgerTrans WHERE DATAAREAID=‘ "+ curExt()+ "‘ "
+ " AND TRANSDATE<CAST(‘ "+ date2str(str2date(’22/02/2006′,123),321,2,3,2,3,4)
+ " ‘ AS datetime) "
+ " AND TRANSDATE> CAST(‘ "+ date2str(str2date(’20/02/2006′,123),321,2,3,2,3,4)
+ " ‘ AS datetime) ";stmt = Con.createStatement();
resultSet = Stmt.executeQuery(sqlString);
resultSet.next();
result = resultSet.getString(1);
if (result)
{
info(result);}
else
{
info( "No record! " );
}
}
Hope it helps.
Fred Shen's AX weblog 