SQL datetime --> System.date

[JumpyNET]

Im trying to retrieve a datetime value from SQL, but Im getting nags about not being able to convert object to date. Directcast and ctype didnt work either. Any advice how this should be done?

Dim TmpTime As Date
Dim Query As String

Query = "SET @maxdate = SELECT MAX(DateAndTime) FROM MyTable"
Dim sqlCmd As SqlCommand = New SqlCommand(Query, sqlConn)
sqlCmd.Parameters.Add("@maxdate", SqlDbType.DateTime)
sqlCmd.ExecuteNonQuery()

TmpTime = sqlCmd.Parameters("@maxdate").Value I GET THE ERROR HERE

PS: Im using SSIS 2005.

 

[PlausiblyDamp]

You could try something like

TmpTime = DirectCast(sqlCmd.Parameters("@maxdate").Value, DateTime)

and see if that helps

 

[JumpyNET]

You could try something like

TmpTime = DirectCast(sqlCmd.Parameters("@maxdate").Value, DateTime)

and see if that helps

I tried this, but it didnt work. Maybe the bug is not the casting but something else.

 

[PlausiblyDamp]

I should have read your original post better http://msdn.microsoft.com/en-us/library/aa326475(VS.71).aspx might be worth a quick look though.

 

[JumpyNET]

Now I tried something simpler and did not even try to read the result. Here is what I tested:

Dim Query As String
Query = "SET @maxdate = (SELECT MAX(Datetime) FROM MachineryCounters)"
Dim sqlCmd As SqlCommand = New SqlCommand()
sqlCmd.Connection = sqlConn
sqlCmd.Parameters.Add("@maxdate", SqlDbType.DateTime)
sqlCmd.CommandText = Query
sqlCmd.ExecuteNonQuery()

The error message states following:
The parameterized query (@maxdate datetime)SET @maxdate = (SELECT MAX(Datetime) FROM Mac expects the parameter @maxdate, which was not supplied.




Reading the datetime like this works:

Dim TmpTime As Date
Dim Query As String
Query = "SELECT MAX(Datetime) FROM MachineryCounters"
Dim sqlCmd As SqlCommand = New SqlCommand()
sqlCmd.Connection = sqlConn
sqlCmd.CommandText = Query
TmpTime = DirectCast(sqlCmd.ExecuteScalar(), Date)

So the casting wasnt the problem. But Id still like to get the parameter approach to work.

 

[PlausiblyDamp]

Have you trie dsetting the parameters direction to either InputOutput or just Output to see if that makes a difference?

 

[JumpyNET]

Have you tried setting the parameters direction to either InputOutput or just Output to see if that makes a difference?

The direction property was indeed the key to solving this problem. Thank you.

Heres the final code, which works:

Dim Query As String
Query = "SET @maxdate = (SELECT MAX(Datetime) FROM MachineryCounters)"
Dim sqlCmd As SqlCommand = New SqlCommand()
sqlCmd.Connection = sqlConn
sqlCmd.Parameters.Add("@maxdate", SqlDbType.DateTime)
 [B]sqlCmd.Parameters("@maxdate").Direction = ParameterDirection.Output[/B]
sqlCmd.CommandText = Query
sqlCmd.ExecuteNonQuery()
 [B]LastTime = DirectCast(sqlCmd.Parameters("@maxdate").Value, Date)[/B]