I have noticed a problem over the past couple of days where my dates submitted to an sql server database are wrong. I have a webpage, where users can book facilities. This webpage takes a name, a date, a start time and an end time(BookingID is required for transactions but generated by database), which I format as a JSON string as follows:
{"BookingEnd":"\/Date(2012-26-03 09:00:00.000)\/","BookingID":1,"BookingName":"client test 1","BookingStart":"\/Date(2012-26-03 10:00:00.000)\/","RoomID":4}
This is then passed to a WCF service, which handles the database insert as follows:
[WebInvoke(Method = "POST", RequestFormat = WebMessageFormat.Json, UriTemplate = "createbooking")]
void CreateBooking(Booking booking);
[DataContract]
public class Booking
{
[DataMember]
public int BookingID { get; set; }
[DataMember]
public string BookingName { get; set; }
[DataMember]
public DateTime BookingStart { get; set; }
[DataMember]
public DateTime BookingEnd { get; set; }
[DataMember]
public int RoomID { get; set; }
}
Booking.svc
public void CreateBooking(Booking booking)
{
BookingEntity bookingEntity = new BookingEntity()
{
BookingName = booking.BookingName,
BookingStart = booking.BookingStart,
BookingEnd = booking.BookingEnd,
RoomID = booking.RoomID
};
BookingsModel model = new BookingsModel();
model.CreateBooking(bookingEntity);
}
Booking Model:
public void CreateBooking(BookingEntity booking)
{
using (var conn = new SqlConnection("Data Source=cpm;Initial Catalog=BookingDB;Integrated Security=True"))
using (var cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText =
@"IF NOT EXISTS ( SELECT * FROM Bookings WHERE BookingStart = @BookingStart AND BookingEnd = @BookingEnd AND RoomID= @RoomID )
INSERT INTO Bookings ( BookingName, BookingStart, BookingEnd, RoomID ) VALUES ( @BookingName, @BookingStart, @BookingEnd, @RoomID )";
cmd.Parameters.AddWithValue("@BookingName", booking.BookingName);
cmd.Parameters.AddWithValue("@BookingStart", booking.BookingStart);
cmd.Parameters.AddWithValue("@BookingEnd", booking.BookingEnd);
cmd.Parameters.AddWithValue("@RoomID", booking.RoomID);
cmd.ExecuteNonQuery();
conn.Close();
}
}
This updates the database but the time ends up "1970-01-01 00:00:02.013" each time I submit the date in the above json format. However, when I do a query in SQL server management studio with the above date format ("YYYY-MM-DD HH:MM:SS.mmm"), it inserts the correct values. Also, if I submit a millisecond datetime to the wcf, the correct date is being inserted. The problem seems to be with the format I am submitting. I am a little lost with this problem. I don't really see why it is doing this. Any help would be greatly appreciated. Thanks.
