Convert Axapta date type value to datetime type value in SQL Server

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.

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

                         + 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! " );

    }

}