SSIS - Datetime Format in SQL Command Variable

Giganews Newsgroups
Subject: SSIS - Datetime Format in SQL Command Variable
Posted by:  pvong (phillip*at*yahoo*dot*com)
Date: Thu, 7 Aug 2008

I know I should post this in the SSIS group, but this gets a lot more
traffic and I'm hoping someone here can help me.

I have a package variable called "TradeDate"  that is a DatetTime data type
and the value comes from a SQL Secect statement.  This has been tested and
the variable is collecting the right value like "8/5/2008".

I have a DataFlow task with a OLE DB Source and it's pulling from a SQL
Command Using a Variable.  I had to use the vearible for th SQL Command.
The Variable is called SQLCommand and it's a string.

Instead of pasting the whole statement, here is the part that's giving me
problems.  If I specify the last part of the statement as
WHERE (DATEADD(DAY, 0, DATEDIFF(DAY, 0, MoxyOrders.OrderDate)) =
CONVERT(DATETIME, '2008-08-05 00:00:00', 102)
I get exactly all the data I want from the date of 8/5/2008.

I need this date to pull from the TradeDate variable.

When I try this:
WHERE (DATEADD(DAY, 0, DATEDIFF(DAY, 0, MoxyOrders.OrderDate)) =
CONVERT(DATETIME,'+ @User::TradeDate +' , 102))
I get this error msg.
An OLE DB record is available.  Source: "Microsoft SQL Native Client"
Hresult: 0x80040E07  Description: "Conversion failed when converting
datetime from character string.".

When I try this:
WHERE (DATEADD(DAY, 0, DATEDIFF(DAY, 0, MoxyOrders.OrderDate)) = '+
@User::TradeDate +')
I get the same err msg.
An OLE DB record is available.  Source: "Microsoft SQL Native Client"
Hresult: 0x80040E07  Description: "Conversion failed when converting
datetime from character string.".

The SQLcommand is a string and I'm trying to pass in a DateTime.  What can I
do so this will work?

Thanks!

Replies