SQL datetime --> System.date

[JumpyNET]

I'm trying to retrieve a datetime value from SQL, but I'm getting nags about not being able to convert object to date. Directcast and ctype didn't 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: I'm 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 didn't 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%28VS.71%29.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 wasn't the problem. But I'd still like to get the parameter approach to work.

Edited June 16, 2009 by JumpyNET

[PlausiblyDamp]

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

[JumpyNET]

Have you tried setting the parameter's 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.

 

Here's 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]