End of Yesterday

For a while, I was using the following expression to set a package DateTime variable, @DateTo, to the end of yesterday:

DATEADD(“ms”, -2, DATEADD(“DAY”, DATEDIFF(“DAY”, (DT_DBTIMESTAMP)0, GETDATE()), (DT_DBTIMESTAMP)0))

Yet, while this worked consistently when run from Visual Studio or the Sql Management Studio, it was occassionally 3 milliseconds off when the package was run from an SQL job or launched from the dos prompt on the database server.  As a workaround, I set the package DateTime variable to the current time and modified the SQLStatement of the SQL Task to select with the end of yesterday:

DECLARE @Date DATETIME;
SET @Date = DATEADD(ms, -2, DATEADD(DAY, DATEDIFF(DAY, 0, @DateTo), 0));
INSERT  INTO …

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: