获取DB将接受的格式的日期 [英] Get the date to a format DB will accept

问题描述

嘿伙计们，我有这个功能：

Hey guys, I have this function:

public DateTime dates(string date, string hour, string min) {
            string dateString = date + " " + hour + ":" + min;
            string[] format = new string[] { "d/M/yyyy h:mm", "d/M/yyyy h:mm:ss", "d-M-yyyy h:mm", "dd-MM-yyyy hh:mm" };
            string finalFormat = "d/M/yyyy h:mm";

            //Set datestring and the format
            DateTime dateTime;
            DateTime.TryParseExact(dateString, format, CultureInfo.InvariantCulture, DateTimeStyles.None, out dateTime);
            dateTime.ToString().Trim().Replace('-', '/');

            return dateTime;
        }

我在这里打电话：

Which i call here:

protected void submit_Click(object sender, EventArgs e)
        { 
            DateTime eventStart = dates(eventStartDate.Text, eventStartHour.Text, eventStartMinute.Text);
            DateTime departure = dates(departureDate.Text, departureHour.Text, departureMinute.Text);
            DateTime arrival = dates(arrivalDate.Text, arrivalHour.Text, arrivalMinute.Text);
            Console.WriteLine(arrival);
            DateTime flightDeparture = dates(flightDepartureStartDate.Text, flightDepartureStartHour.Text, flightDepartureStartMinute.Text);
            DateTime flightReturn = dates(flightReturnStartDate.Text, flightReturnStartHour.Text, flightReturnStartMinute.Text);

            byte[] file = (byte[])Session["File"];
            string filename = System.IO.Path.GetFileName(AsyncFileUpload1.FileName);
            string ConStr = ConfigurationManager.ConnectionStrings["FORgestIT"].ToString();

            SqlConnection SQLConn = new SqlConnection(ConStr); //The SQL Connection              

            SqlCommand SQLCmd = new SqlCommand();
            SQLCmd.Connection = SQLConn;
            SQLCmd.CommandType = CommandType.Text;

            //SQLCmd.CommandText = "insert into Event values(@Name,@Project,@Objectives,@City,@Country,@Event_Start,@Departure,@Arrival,@Registration,@National_Transportation,@Accomodation,@AC_NumberNights,@AC_PreferHotel,@AC_PreferHotelURL,@Flight,@FL_Departure,@FL_Depart_Prefer,@FL_Depart_URL,@FL_Ret_Date,@FL_Return,@FL_Ret_Prefer,@FL_Ret_URL,@Notes,@File,@Status)";

            // ACCORDING TO OG's SOLUTION OP CHANGED THE PREVIOUS LINE TO THIS
            SQLCmd.CommandText = "INSERT INTO Event (Name, Project, Objectives, City, Country, Event_Start, Departure, Arrival, Registration, National_Transportation, Accomodation, AC_NumberNights, AC_PreferHotelURL, Flight, FL_departure, FL_Depart_Prefer, FL_Depart_URL, FL_Return, FL_Ret_Prefer, FL_RET_URL, Notes, File, Status) VALUES (@Name,@Project,@Objectives,@City,@Country,@Event_Start,@Departure,@Arrival,@Registration,@National_Transportation,@Accomodation,@AC_NumberNights,@AC_PreferHotel,@AC_PreferHotelURL,@Flight,@FL_Departure,@FL_Depart_Prefer,@FL_Depart_URL,@FL_Return,@FL_Ret_Prefer,@FL_Ret_URL,@Notes,@File,@Status)";

            SQLCmd.Parameters.Clear();
            SQLCmd.Parameters.AddWithValue("@Name", name.Text);
            SQLCmd.Parameters.AddWithValue("@Project", project.Text);
            SQLCmd.Parameters.AddWithValue("@Objectives", objectives.Text);
            SQLCmd.Parameters.AddWithValue("@City", venueCity.Text);
            SQLCmd.Parameters.AddWithValue("@Country", venueCountry.Text);
            SQLCmd.Parameters.AddWithValue("@Event_Start", eventStart);
            SQLCmd.Parameters.AddWithValue("@Departure", departure);
            SQLCmd.Parameters.AddWithValue("@Arrival", arrival);
            SQLCmd.Parameters.AddWithValue("@Registration", registrationInformation.Text);
            SQLCmd.Parameters.AddWithValue("@National_Transportation", rdlYesNo.SelectedValue);
            SQLCmd.Parameters.AddWithValue("@Accomodation", accomodation.SelectedValue);
            SQLCmd.Parameters.AddWithValue("@AC_NumberNights", numberOfNights.Text);
            SQLCmd.Parameters.AddWithValue("@AC_PreferHotel", preferredHotel.Text);
            SQLCmd.Parameters.AddWithValue("@AC_PreferHotelURL", preferredHotelURL.Text);
            SQLCmd.Parameters.AddWithValue("@Flight", flight.SelectedValue);
            SQLCmd.Parameters.AddWithValue("@FL_Departure", flightDeparture);
            SQLCmd.Parameters.AddWithValue("@FL_Depart_Prefer", flightDeparturePreferred.Text);
            SQLCmd.Parameters.AddWithValue("@FL_Depart_URL", flightDeparturePreferredURL.Text);
            SQLCmd.Parameters.AddWithValue("@FL_Return", flightReturn);
            SQLCmd.Parameters.AddWithValue("@FL_Ret_Prefer", flightReturnPreferred.Text);
            SQLCmd.Parameters.AddWithValue("@FL_Ret_URL", flightReturnPreferredURL.Text);
            SQLCmd.Parameters.AddWithValue("@Notes", notes.Text);
            SQLCmd.Parameters.AddWithValue("@File", file);
            SQLCmd.Parameters.AddWithValue("@Status", "Pending");

            if (SQLConn.State == ConnectionState.Closed)
            {
                SQLConn.Open();
            }

            SQLCmd.ExecuteNonQuery();
            SQLConn.Close();

            string url = "Default.aspx";
            ClientScript.RegisterStartupScript(this.GetType(), "callfunction", "alert('Your request form was saved correctly!');window.location.href = '" + url + "';", true);
        }

这里的问题是我试图将DateTime变量保存到数据库中但它不接受它们的格式......他们的输出像{14-Jan-15 02:10:00}，DB要求像dd / MM / yyyy这样的东西......：mm：ss我该怎么办？

The problem here is I'm trying to save the DateTime variables into the DB but it's not accepting their format... they have an output like {14-Jan-15 02:10:00} and DB ask for something like dd/MM/yyyy hh:mm:ss what should I do?

推荐答案

由于您通过参数传递日期 - 正确地，您始终应该 - 问题不在于日期格式，因为DateTime值作为DateTime传递，而不是作为格式化字符串传递。相反，它从指定的时间点开始直接传递数毫秒，并且可以直接插入到SQL DATETIME字段中。



问题更可能发生因为你没有列出你要插入数据的列：

Since you are passing your dates via parameters - correctly, you always should - the problem isn't with the date format, because the DateTime value is being passed as a DateTime, not as a formatted string. Instead, it is passed directly as a number of milliseconds since a nominated point in time, and can be inserted directly into an SQL DATETIME field.

The problem is more likely to be that you don't list the columns into which you are INSERTing your data:

SQLCmd.CommandText = "insert into Event values(@Name,@Project,...

这意味着@Name将进入第一列SQL查找，@ Project注入第二列，依此类推。如果您的SQL表定义包含任何其他列（例如正常的ID列）或列因任何原因重新排序，它将尝试插入将值放入错误的列中，这意味着它会尝试自动转换它们。

尝试列出列名：

Which means that @Name will go into teh first columns SQL finds, @Project into the second, and so forth. If your SQL table definition includes any other columns (such as an ID column as is normal) or the columns get reordered for any reason, it will try to insert the values into the wrong columns, and that means it will try to auto convert them.
Try listing the column names:

SQLCmd.CommandText = "INSERT INTO Event ([Name], Project, ...) VALUES (@Name,@Project,...

并查看问题是否消失。

除了解决方案1之外，还有关于尝试

As an aside to Solution 1 and in addition to the comment about trying

SQLCmd.Parameters.Add("@Event_Start", SqlDbType.DateTime).Value = eventStart;

我总是建议在使用日期时使用与文化无关的格式 - 即一个明确的格式。



考虑以下sql

I always advise using culture-agnostic formats when using dates - i.e. an unambiguous format.

Consider the following sql

create table demo
(
  demoDate datetime
  )
insert into demo VALUES('10-01-2015')

在英国看起来我正在插入1月10日2015进入数据库，但SQL Server实例实际上是在美国托管的，所以当我选择那些数据时，我实际上会回来

In the UK that looks like I'm inserting "10th January 2015" into the database, but the SQL Server instance is actually hosted in the USA so when I select that data I actually get back

October, 01 2015 00:00:00+0000

但如果我使用

But if I use

insert into demo VALUES('10-JAN-2015')

我现在回到我的预期

January, 10 2015 00:00:00+0000

真的很抱歉所有那些工作的人我的坏！我读了DB一千次，现在注意到它说酒店的Hotal insteas ...真的很抱歉伙伴们：s

Really sorry about all that work guys my bad! I read DB a thousand times and just noticed now it said Hotal insteas of Hotel... really sorry guys :s

这篇关于获取DB将接受的格式的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！