Basic Date Calculations

 

Hey Friends,

This Post is all about DATE. I saw many people getting stuck as whenever there needs some date calculation logic and the only reason behind this is that there are too many permutation and combinations that comes into play whenever we start creating our logic. So I just though to add a post where I can add all those very commonly needed outcomes.

FirstDayOfMonth := CALCDATE (‘<CM-1M+1D>’, Today);

LastDayOfMonth := CALCDATE (‘<CM>’, Today);

FirstDayOfPreviousMonth := CALCDATE (‘<CM-2M+1D>’, Today);

LastDayOfPreviousMonth := CALCDATE(‘CM’,CALCDATE(‘-CM-1D’,TODAY));

Day := DATE2DMY(TODAY, 1);

Month := DATE2DMY(TODAY, 2);

Year := DATE2DMY(TODAY, 3);

WD4 The next 4th day of a week (Thursday)
CM+10D Current Month +10D
10D 10 Days from today
CW Last date of the current week (Sunday)
CM Last day of the current month
CQ Last day of the current quarter
CY Last day of the current year
1Y    1 Year ago from today
CY-2Y+1D First Day of last fiscal year
CY-1Y Last Day of last fiscal year
2W 2 weeks from today
D10 The next 10th day of the month

I will go on increasing this list. This will anyhow save our time.

There’s a virtual Table called DATE. I always remind and recommend everyone to use this Table, it really makes the work a more easier. 

CHEERS

Call Stored Procedure from Navision (DotNet Variable)

 

SQLCommand := SQLCommand.SqlCommand();
SQLCommand.CommandType := SQLCommand.CommandType.StoredProcedure;
SQLConnection := SQLConnection.SqlConnection(‘Server=’+’SERVERNAME’+’;’
+ ‘Database=’+’DB Name’+’;’
+ ‘Uid=’+’UID’+’;’
+ ‘Pwd=’+’PWD’+’;’);
SQLConnection.Open;

SQLCommand.CommandText(‘Metro_InsertUpdateOrderInfoEventTriggered’);
SQLCommand.Connection := SQLConnection;

SqlParamCycleCode := SqlParamCycleCode.SqlParameter();
SqlParamCycleCode.ParameterName := ‘@P1’;
SqlParamCycleCode.DbType := SQLDbType.String;
SqlParamCycleCode.Direction := SqlParamCycleCode.Direction.Input;
SqlParamCycleCode.Value:= eventcode;
SQLCommand.Parameters.Add(SqlParamCycleCode);

SqlParamCycleCode := SqlParamCycleCode.SqlParameter();
SqlParamCycleCode.ParameterName := ‘@P2’;
SqlParamCycleCode.DbType := SQLDbType.String;
SqlParamCycleCode.Direction := SqlParamCycleCode.Direction.Input;
SqlParamCycleCode.Value:= 0;
SQLCommand.Parameters.Add(SqlParamCycleCode);

SqlParamCycleCode := SqlParamCycleCode.SqlParameter();
SqlParamCycleCode.ParameterName := ‘@P3’;
SqlParamCycleCode.DbType := SQLDbType.String;
SqlParamCycleCode.Direction := SqlParamCycleCode.Direction.Input;
SqlParamCycleCode.Value:= 0;
SQLCommand.Parameters.Add(SqlParamCycleCode);

SqlParamCycleCode := SqlParamCycleCode.SqlParameter();
SqlParamCycleCode.ParameterName := ‘@P4’;
SqlParamCycleCode.DbType := SQLDbType.String;
SqlParamCycleCode.Direction := SqlParamCycleCode.Direction.Input;
SqlParamCycleCode.Value:= 0;
SQLCommand.Parameters.Add(SqlParamCycleCode);

SqlParamCycleCode := SqlParamCycleCode.SqlParameter();
SqlParamCycleCode.ParameterName := ‘@P5’;
SqlParamCycleCode.DbType := SQLDbType.String;
SqlParamCycleCode.Direction := SqlParamCycleCode.Direction.Input;
SqlParamCycleCode.Value:= WebUserId;
SQLCommand.Parameters.Add(SqlParamCycleCode);

SqlParamCycleCode := SqlParamCycleCode.SqlParameter();
SqlParamCycleCode.ParameterName := ‘@P6’;
SqlParamCycleCode.DbType := SQLDbType.String;
SqlParamCycleCode.Direction := SqlParamCycleCode.Direction.Input;
SqlParamCycleCode.Value:= NoOfOrder;
SQLCommand.Parameters.Add(SqlParamCycleCode);

SqlParamCycleCode := SqlParamCycleCode.SqlParameter();
SqlParamCycleCode.ParameterName := ‘@P7’;
SqlParamCycleCode.DbType := SQLDbType.String;
SqlParamCycleCode.Direction := SqlParamCycleCode.Direction.Input;
SqlParamCycleCode.Value:= TriggerModule;
SQLCommand.Parameters.Add(SqlParamCycleCode);

SqlParamCycleCode := SqlParamCycleCode.SqlParameter();
SqlParamCycleCode.ParameterName := ‘@P8’;
SqlParamCycleCode.DbType := SQLDbType.String;
SqlParamCycleCode.Direction := SqlParamCycleCode.Direction.Input;
SqlParamCycleCode.Value:= TriggerSource;
SQLCommand.Parameters.Add(SqlParamCycleCode);
SqlParamCycleCode := SqlParamCycleCode.SqlParameter();
SqlParamCycleCode.ParameterName := ‘@ActionUser’;
SqlParamCycleCode.DbType := SQLDbType.String;
SqlParamCycleCode.Direction := SqlParamCycleCode.Direction.Input;
SqlParamCycleCode.Value:= ActionUser;
SQLCommand.Parameters.Add(SqlParamCycleCode);

SqlParamCycleCode := SqlParamCycleCode.SqlParameter();
SqlParamCycleCode.ParameterName := ‘@P9’;
SqlParamCycleCode.DbType := SQLDbType.String;
SqlParamCycleCode.Direction := SqlParamCycleCode.Direction.Input;
SqlParamCycleCode.Value:= OrderNo;
SQLCommand.Parameters.Add(SqlParamCycleCode);

SqlParamCycleCode := SqlParamCycleCode.SqlParameter();
SqlParamCycleCode.ParameterName := ‘@P10’;
SqlParamCycleCode.DbType := SQLDbType.String;
SqlParamCycleCode.Direction := SqlParamCycleCode.Direction.Input;
SqlParamCycleCode.Value:= Custno;
SQLCommand.Parameters.Add(SqlParamCycleCode);

SqlParamCycleCode := SqlParamCycleCode.SqlParameter();
SqlParamCycleCode.ParameterName := ‘@P11’;
SqlParamCycleCode.DbType := SQLDbType.String;
SqlParamCycleCode.Direction := SqlParamCycleCode.Direction.Input;
SqlParamCycleCode.Value:= ”;
SQLCommand.Parameters.Add(SqlParamCycleCode);

SqlParamCycleCode := SqlParamCycleCode.SqlParameter();
SqlParamCycleCode.ParameterName := ‘@P12’;
SqlParamCycleCode.DbType := SQLDbType.String;
SqlParamCycleCode.Direction := SqlParamCycleCode.Direction.Input;
SqlParamCycleCode.Value:= ”;
SQLCommand.Parameters.Add(SqlParamCycleCode);

SqlParamCycleCode := SqlParamCycleCode.SqlParameter();
SqlParamCycleCode.ParameterName := ‘@P13’;
SqlParamCycleCode.DbType := SQLDbType.String;
SqlParamCycleCode.Direction := SqlParamCycleCode.Direction.Input;
SqlParamCycleCode.Value:= ”;
SQLCommand.Parameters.Add(SqlParamCycleCode);
SQLCommand.ExecuteNonQuery;

SQLConnection.Close;
CLEAR(SQLCommand);
CLEAR(SQLConnection);
SQLConnection – System.Data.SqlClient.SqlConnection.’System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′

SQLCommand – System.Data.SqlClient.SqlCommand.’System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′

SqlParamCycleCode – System.Data.SqlClient.SqlParameter.’System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′

SQLDbType – System.Data.DbType.’System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′

Call Stored Procedure from Dynamics Navision (Automation Variable)

 

IF ISCLEAR(ADOConnection) THEN
CREATE(ADOConnection,FALSE,TRUE);

ADOConnection.ConnectionString:=
‘Driver={SQL Server};’
+ ‘Server=’+’SERVER NAME’+’;’
+ ‘Database=’+’DATABASE NAME’+’;’
+ ‘Uid=’+’SQLUID’+’;’
+ ‘Pwd=’+’SQLPWD’+’;’;

ADOConnection.Open;

IF ISCLEAR(ADOCommand) THEN
CREATE(ADOCommand,FALSE,TRUE);

VarActiveConnection := ADOConnection;
ADOCommand.ActiveConnection := VarActiveConnection;
ADOCommand.CommandText := ‘Metro_InsertUpdateOrderInfoEventTriggered’;
ADOCommand.CommandType := 4;
ADOCommand.CommandTimeout := 0;

ADOParameter:=ADOCommand.CreateParameter(‘@EventCode’,3,1,3,eventcode);
ADOCommand.Parameters.Append(ADOParameter);

ADOParameter:=ADOCommand.CreateParameter(‘@P1’,3,1,3,0);
ADOCommand.Parameters.Append(ADOParameter);

ADOParameter:=ADOCommand.CreateParameter(‘@P2’,3,1,3,0);
ADOCommand.Parameters.Append(ADOParameter);

ADOParameter:=ADOCommand.CreateParameter(‘@P3’,3,1,3,0);
ADOCommand.Parameters.Append(ADOParameter);

ADOParameter:=ADOCommand.CreateParameter(‘@P4’,3,1,3,WebUserId);
ADOCommand.Parameters.Append(ADOParameter);
ADOParameter:=ADOCommand.CreateParameter(‘@P5’,3,1,3,NoOfOrder);
ADOCommand.Parameters.Append(ADOParameter);

ADOParameter:=ADOCommand.CreateParameter(‘@P6’,200,1,50,TriggerModule);
ADOCommand.Parameters.Append(ADOParameter);

ADOParameter:=ADOCommand.CreateParameter(‘@P7’,200,1,50,TriggerSource);
ADOCommand.Parameters.Append(ADOParameter);

ADOParameter:=ADOCommand.CreateParameter(‘@P8’,200,1,50,ActionUser);
ADOCommand.Parameters.Append(ADOParameter);

ADOParameter:=ADOCommand.CreateParameter(‘@p9’,200,1,50,OrderNo);
ADOCommand.Parameters.Append(ADOParameter);

ADOParameter:=ADOCommand.CreateParameter(‘@P10’,200,1,50,Custno);
ADOCommand.Parameters.Append(ADOParameter);

ADOParameter:=ADOCommand.CreateParameter(‘@P11’,200,1,100,”);
ADOCommand.Parameters.Append(ADOParameter);

ADOParameter:=ADOCommand.CreateParameter(‘@P12’,200,1,100,”);
ADOCommand.Parameters.Append(ADOParameter);

ADOParameter:=ADOCommand.CreateParameter(‘@P13’,200,1,100,”);
ADOCommand.Parameters.Append(ADOParameter);
ADOCommand.Execute;
ADOConnection.Close;
CLEAR(ADOConnection);
ADOCommand  – Automation ‘Microsoft ActiveX Data Objects 2.8 Library’.Command
ADOParameter – Automation ‘Microsoft ActiveX Data Objects 2.8 Library’.Parameter
VarActiveConnection – Variant
ADOConnection Automation –  Microsoft ActiveX Data Objects 2.8 Library’.Connection