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<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.
Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: