Datetime Format in SQL Command Variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Datetime Format in SQL Command Variable

Author

Topic

pvong
Yak Posting Veteran

58 Posts

Posted - 2008-08-07 : 21:32:56

Here's the setup.

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!

------------------------------
Using VS2008 / Learning in VB.Net 2.0 / Win2003 / SQL 2005 w SP2
Be kind to the newbies because you were once there.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-08-08 : 02:13:05

Hi pvong,
at first please find a way to display your SQLCommand-variable so you/we can see what happens.

Webfred

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-08-08 : 08:16:56

Try this:

"select OrderDate from myTable 
WHERE (DATEADD(DAY, 0, DATEDIFF(DAY, 0, MoxyOrders.OrderDate)) = CONVERT(varchar(30),'" + (DT_STR,30,1252)@[User::TradeDate] + "',102))"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources