Best Practices to Derive Start and End Dates for a SQL Server Stored Procedure in a SSIS Package

Question

So I need to derive Start and End Dates for say a Monday run that will encompass a Start Date of the Friday before to Sunday. The data I am going after is indeed a DATETIME data column type.

So I am wondering what is best practice for approaching this keeping in mind that we may also have to adjust for GMT using Azure.

Should the derived Start and End Date be a DATETIME Stamp and what about the SQL Server Stored Procedure? Should that just be a DATE since we really are going after Dates? Or should it be consistent using DATETIME values across the board.

Just want to know how others approach this.

Thanks for your review and am hopeful for a good reply.

Answer 1

I guess I'm asking if our SQL Server Table Data Column where we need to filter by Date is defined with data type DATETIME, should we derive the DATETIME in our SSIS Package to pass to the SQL Server Stored Procedure? I think I read somewhere that using a Variable Type DateTime in a SSIS Package doesn't necessarily work as one might expect and to use Strings rather than DateTime.

I'm just looking for best practices when deriving a Start Date and End Date, storing it as a SSIS Variable, and then using that Variable as a Parameter for the Exec of the SQL Server Stored Procedure.

I hope your comment didn't kill my post from being answered.