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;
    con = new ODBCConnection(loginProperty);
    sqlString =   "SELECT * FROM LedgerTrans WHERE DATAAREAID="
                         + " 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);;
    result = resultSet.getString(1);
    if (result)
        info( "No record! " );

Hope it helps.
This entry was posted in Axapta Development. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s