Hello,

I am creating a web server which lets me insert into a table. The user invokes the specific ID and its meant to append to the table with that ID, startdate and an enddate.

<WebMethod()> _
    Public Function reserveVehicle1(ByVal vID As Integer) As Boolean
        Dim strInsert As String
        Dim conn As SqlConnection
        Dim cmd As SqlCommand
        Dim now As DateTime = System.DateTime.Now
        Dim now5 As DateTime = System.DateTime.Now.AddMinutes(5)
        Dim connString As String

        connString = "server=xxxx"
        strInsert = String.Format("Insert into Reservation (VehicleID, startDate, enddate) VALUES ('{0}','{1}','{2}')", vID, now, now5)

It was working perfectly up til today until an error came up whilst attemping it:
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Upon looking at the Reservation table, it seems that its appending it with the DD and the MM the opposite way around.

For example, the last time I appended it (when it worked) was two days ago (11th of December). Here are the last several rows with the information:

20 2 12/08/2011 19:27:14 12/08/2011 19:32:14
22 2 12/08/2011 19:28:29 12/08/2011 19:33:29
24 1 12/08/2011 19:28:37 12/08/2011 19:33:37
25 1 12/08/2011 19:28:38 12/08/2011 19:33:38
26 3 12/11/2011 16:45:13 12/11/2011 16:50:13
27 3 12/11/2011 16:48:42 12/11/2011 16:53:42

The first two columns are BookingID and VehicleID. Its saying that its gone from August (8) to November (11) in 3 days. Its now not working because its a DD in the month higher than 12 so its screwing up.

So I am putting it to you guys on how I can fix this problem. I created a simple .aspx page with a label showing system.datetime.now and it was showing the correct datetime (13/12/2011 19:52:08) as of now.

Cheers.

Hi craigus12

Can you post the table structure for me? Fields, datatypes etc...

Cheers

Yeah its a table named Reservation.

BookingID - int that increments
VehicleID - int
startDate - datetime
endDate - datetime

Thanks!

so looking at the date format in the database

20 2 12/08/2011 19:27:14 12/08/2011 19:32:14
22 2 12/08/2011 19:28:29 12/08/2011 19:33:29
24 1 12/08/2011 19:28:37 12/08/2011 19:33:37
25 1 12/08/2011 19:28:38 12/08/2011 19:33:38
26 3 12/11/2011 16:45:13 12/11/2011 16:50:13
27 3 12/11/2011 16:48:42 12/11/2011 16:53:42

it appears like month/day/year

your code returns 13/12/2011 19:52:08

Im assuming that this format is day/month/year since that is a valid date

what I think is happening is that when the date from your code hits the database it is interpruted as month/day/year (using the date your code provides 13/12/2011 this is an invalid date since there is not a 13th month in any year I have yet experienced :P

Regional settings can be the cause of this type of issue if the regional configuration of the database server is different from the web server for example

try formatting the now and now5 DateTime values before inserting data into database so that the date is formatted yyyy-mm-dd

so for example 2011-12-13 19:52:08

this meaning 13 December 2011

using that format you should get the correct/expected date inserted into the database

Thanks ecsl, so its the settings of the whole database which would change my startdate and enddate to dd/mm/yyyy.

is there any way to format it manually so it inserts into the table? not bothered how, I just need it so it lets me append the current date time, and then the current date time (with 5 minutes added on).


EDIT: Right then, I have managed to append my data to the table again but now its coming up (on both of the startdate and enddate) as 13/12/2011 00:00:00.

What I changed was:

Dim now = DateTime.Now.ToString("yyyyMMdd")
        Dim now5 = DateTime.Now.AddMinutes(5).ToString("yyyyMMdd")
        Dim connString As String
        connString = "xxxxxxxx"
        strInsert = String.Format("Insert into Reservation (VehicleID, startDate, enddate) VALUES ('{0}','{1}','{2}')", vID, now, now5)
Dim now5 = DateTime.Now.AddMinutes(5).ToString("yyyy-MM-dd HH:mm:ss")

So as above just make sure you specify the format for the time also since is a DateTime datatype if that is not specified in the format you will end up with zeros for all time portions

use ("yyyy-MM-dd HH:mm:ss") for both now and now5 variables

Thanks ecsl, you have been most helpful!

Works fine now. Thanks so much.