Inserting DateTime to SQL Server

[craigus12]

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.

[ecsl]

Hi craigus12

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

Cheers

[craigus12]

Yeah its a table named Reservation.

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

Thanks!

[ecsl]

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

[ecsl]

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

[craigus12]

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)

[ecsl]

Dim now5 = DateTime.Now.AddMinutes(5).ToString("yyyy-MM-dd HH:mm:ss")

[ecsl]

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

[craigus12]

Thanks ecsl, you have been most helpful!

Works fine now. Thanks so much.