Question Problem passing just the Time parameter to a query

[divjoy]

Hi, I am using vb.net 2010 express.

I am connecting to a sql server database and trying to pass a time parameter to a query.

But because the time variable in vb is '05:00:00' I get a sql date time overflow error. IT seems to be expexting a date and time !

Is there some easy way of adding the time to a default date say 01/01/01 in vb.net then I can strip off the date in my query or stored procedure and add time to the Time column in my database

Thanking you

 

[JuggaloBrotha]

You'll need to pass it a DateTime because Sql Server (as far as I know) doesn't have a Time datatype.
What you can do in your program is create a new DateTime variable and set the Hours, Minutes, Seconds manually.
Like:

Dim NewDateTime As DateTime = DateTime.Now
With NewDateTime
    .Hours = 5
    .Minutes = 0
    .Seconds = 0
End With

 

[jmcilhinney]

Have you tried using a TimeSpan?

 

[jmcilhinney]

...Sql Server (as far as I know) doesn't have a Time datatype.

Since 2008.

https://msdn.microsoft.com/en-us/library/bb677243.aspx

There is a corresponding SqlDbType.Time value but MSDN doesn't indicate that it maps to TimeSpan, so I'm not sure.

https://msdn.microsoft.com/en-us/library/system.data.sqldbtype(v=vs.110).aspx

 

[JohnH]

SqlDbType.Time value but MSDN doesn't indicate that it maps to TimeSpan

This article says is does: Date and Time Data

 

[divjoy]

Hi All,

I found the solution... vb has a datatyes Date and DateTime but NOT Time on its own, so when passing as a parameter to a sql stored procedure I need to use the datetime method 'TimeOfDay' !

I tried all the other Timepsan etc but didn't work as TimeSpan is a length of time as opposed to a specific time of the day!
Code snippet
...

        Dim Duration As String
[B]        Dim OldApptTime, NewApptTime As DateTime[/B]
        Dim ApptDate As Date
        Dim OldStaffID As Integer
        Dim count As Integer
        Try
            Duration = FrmViewCalendar.Duration
            ApptDate = FrmViewCalendar.ApptDate
            OldApptTime = FrmViewCalendar.ApptTime
            NewApptTime = Me.tbApptTime.Text
            cmd = New SqlCommand("UPDATE TblAppointments SET ApptTime =@NewApptTime, StaffID = @NewStaffID " & _
                                 " WHERE ClientID = @ClientID AND ApptDate = @ApptDate AND ApptTime = @OldApptTime " & _
                                 " AND StaffID = @OldStaffID", conn)
            cmd.Parameters.Add("@ClientID", SqlDbType.Int).Value = ClientID 
            cmd.Parameters.Add("@ApptDate", SqlDbType.Date).Value = ApptDate  
[B]            cmd.Parameters.Add("@NewApptTime", SqlDbType.Time).Value = NewApptTime.TimeOfDay [/B]
            cmd.Parameters.Add("@NewStaffID", SqlDbType.Int).Value = Me.cbStaff.SelectedValue   'Local Var
            cmd.Parameters.Add("@Duration", SqlDbType.Int).Value = Duration 
[B]            cmd.Parameters.Add("@OldApptTime", SqlDbType.Time).Value = OldApptTime.TimeOfDay [/B]
            cmd.Parameters.Add("@OldStaffID", SqlDbType.Int).Value = OldStaffID  
            cmd.Connection = conn
            conn.Open()
            count = Convert.ToInt32(cmd.ExecuteNonQuery())
            conn.Close()
            cmd.Dispose()
            Me.Close()

 

[JohnH]

DateTime.TimeOfDay Property (System)
You may notice the type of that property is TimeSpan

 

[jmcilhinney]

Hi All,

I found the solution... vb has a datatyes Date and DateTime but NOT Time on its own, so when passing as a parameter to a sql stored procedure I need to use the datetime method 'TimeOfDay' !

I tried all the other Timepsan etc but didn't work as TimeSpan is a length of time as opposed to a specific time of the day!
Code snippet
...

        Dim Duration As String
[B]        Dim OldApptTime, NewApptTime As DateTime[/B]
        Dim ApptDate As Date
        Dim OldStaffID As Integer
        Dim count As Integer
        Try
            Duration = FrmViewCalendar.Duration
            ApptDate = FrmViewCalendar.ApptDate
            OldApptTime = FrmViewCalendar.ApptTime
            NewApptTime = Me.tbApptTime.Text
            cmd = New SqlCommand("UPDATE TblAppointments SET ApptTime =@NewApptTime, StaffID = @NewStaffID " & _
                                 " WHERE ClientID = @ClientID AND ApptDate = @ApptDate AND ApptTime = @OldApptTime " & _
                                 " AND StaffID = @OldStaffID", conn)
            cmd.Parameters.Add("@ClientID", SqlDbType.Int).Value = ClientID 
            cmd.Parameters.Add("@ApptDate", SqlDbType.Date).Value = ApptDate  
[B]            cmd.Parameters.Add("@NewApptTime", SqlDbType.Time).Value = NewApptTime.TimeOfDay [/B]
            cmd.Parameters.Add("@NewStaffID", SqlDbType.Int).Value = Me.cbStaff.SelectedValue   'Local Var
            cmd.Parameters.Add("@Duration", SqlDbType.Int).Value = Duration 
[B]            cmd.Parameters.Add("@OldApptTime", SqlDbType.Time).Value = OldApptTime.TimeOfDay [/B]
            cmd.Parameters.Add("@OldStaffID", SqlDbType.Int).Value = OldStaffID  
            cmd.Connection = conn
            conn.Open()
            count = Convert.ToInt32(cmd.ExecuteNonQuery())
            conn.Close()
            cmd.Dispose()
            Me.Close()

As JohnH says, that code DOES use TimeSpan values so when you say that you tried TimeSpan and it didn't work, what you actually mean is that you did it wrong. As for TimeSpan being a length of time rather than a time of day, what do you think a time of day is but a length of time from midnight? When you get the TimeOfDay property of a DateTime that is EXACTLY what you're getting: a TimeSpan that represents the length of time since midnight on that day.