Member Avatar for NYCRAVEN

I have written a VB reservations program that writes back to a SQL 2008 database. When I write the reservation event back to the data base, I also write the reservation 'end time' back to the database as reservationtime + 4 hours in order to block off the event resource. the program works fine until I try to book an event at8:00 PM or later. When I try to book an event at 8 PM or after the SQL database throws an error, 'System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM'. I guess this is because the booked event then continues into the next day. I need to be able to block of the time space as reservations are accepted up to 2:00 am. Is there a work around?

  • 1 Contributor
  • 2 Replies
  • 382 Views
  • 23 Hours Discussion Span
  • Latest Post Latest Post by NYCRAVEN

Dani AI

Generated

Good diagnosis by — the real problem was that the time-only value carried a .NET default date (year 1) and that date is outside SQL Server’s DATETIME acceptance. Stripping the text with Replace may make the display look right, but it’s brittle and leaves you writing invalid date values to the database.

A safer approach:

  • Build a full DateTime by combining the chosen calendar date and the chosen time, then AddHours(4). That way AddHours will correctly roll into the next day when needed.
  • Pass DateTime values as parameters (use SqlDbType.DateTime2 on SQL Server 2008+) instead of concatenating strings. That avoids culture/format problems and prevents accidental insertion of an invalid date value.

Example (VB.NET):

Dim chosenDate As Date = ReservationDateRequestedDateTimePicker.Value.Date
Dim chosenTime As TimeSpan = TimeSpan.Parse(ReservationTimeRequestedComboBox.Text)
Dim startDt As Date = chosenDate.Add(chosenTime)
Dim endDt As Date = startDt.AddHours(4)

Using cmd As New SqlCommand("INSERT INTO Reservations (StartTime, EndTime) VALUES (@s,@e)", conn)
    cmd.Parameters.Add("@s", SqlDbType.DateTime2).Value = startDt
    cmd.Parameters.Add("@e", SqlDbType.DateTime2).Value = endDt
    cmd.ExecuteNonQuery()
End Using

Notes: don’t rely on string hacks to fix date math. If you ever need to store extremely old dates, use DATETIME2 (it supports year 0001), or use DateTimeOffset for timezone-aware bookings. Also test edge cases like DST transitions and midnight rollovers.

Member Avatar for NYCRAVEN

here is the code I am using to creat the scheduled time block:

'Create apptEnd value by adding 4 hours to start reservation time requested
        Dim mperdtm As Date = TimeValue(ReservationTimeRequestedComboBox.Text)
        Dim mperNew As Date = DateAdd(DateInterval.Hour, 4, mperdtm)
        Dim strDT As Date = ReservationDateRequestedDateTimePicker.Text
        Dim cEndDT As Date = mperNew

   ApptEndTimeTextBox.Text = cEndDT
   ReservationTimeEndTextBox.Text = mperNew
Member Avatar for NYCRAVEN

i got it worked out, the time variable was adding "1/2/0001" to the begining of the entry...so i wrote it out to a string and did a .Replace("1/2/0001", "") to strip it off.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.