c# - 如何合并日期时间类型字段和浮点类型字段。之后解析为日期时间数据类型字段(daypilot 日历)

Question

我的老师告诉我选择 columndate cocat columntime 作为 column3，然后将 column3 解析为 datetime 数据类型。他说我不能更改数据库数据类型，我需要使用这 4 列字段 start_date、end_date、start_time 和 end_time，因为它在服务器中，所以他告诉我这样做。

他的意思是在代码中将start_date字段和start_time字段合并为一列，然后将其转换为datetime类型也在代码中完成。结束日期字段和结束时间字段也是如此。这意味着，例如，记录 1 的开始日期和开始时间将变为 3/4/2014 9:00:00AM，记录 1 的结束日期和结束时间将变为 3/4/2014 12:00:00PM。

下面是我的代码，它用于调度程序/时间表，称为 daypilot。那么可以帮助我查询转换吗？在下面的代码中有一个 start_date 和 end_date，将被最终解析的 datetime 数据类型替换。帮助男人

我的日程表

我的 DAYPILOT 控制属性

如果代码有效，理想的输出...这就是我想要实现的。我已经使用表格中的两个日期时间列对此进行了测试。

public partial class number2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            DayPilotCalendar1.StartDate = DayPilot.Utils.Week.FirstWorkingDayOfWeek(new DateTime(2014, 03, 24));
            DayPilotCalendar1.DataSource = dbGetEvents(DayPilotCalendar1.StartDate, DayPilotCalendar1.Days);
            DataBind();
        }
    }

    private DataTable dbGetEvents(DateTime start, int days)
    {        
        string constr = ConfigurationManager.ConnectionStrings["ProjectConnectionString"].ConnectionString;
        SqlDataAdapter da = new SqlDataAdapter("SELECT ID, PURPOSE, [START_DATE], [END_DATE] FROM [Schedule]  WHERE NOT (([END_DATE] <= @start) OR ([START_DATE] >= @end))", constr);
        da.SelectCommand.Parameters.AddWithValue("start", start);
        da.SelectCommand.Parameters.AddWithValue("end", start.AddDays(days));
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;

    }
}

遵循@Trafz 代码，它成功了。这就是答案。

public partial class number2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            DayPilotCalendar1.StartDate = DayPilot.Utils.Week.FirstWorkingDayOfWeek(new DateTime(2014, 04, 03));
            //DayPilotCalendar1.StartDate = DayPilot.Utils.Week.FirstWorkingDayOfWeek(DateTime.Now.AddDays(-(int)DateTime.Now.DayOfWeek).Date);
            DayPilotCalendar1.DataSource = dbGetEvents(DayPilotCalendar1.StartDate, DayPilotCalendar1.Days);
            DataBind();
        }

    }

    private DataTable dbGetEvents(DateTime start, int days)
    {
        string constr = ConfigurationManager.ConnectionStrings["ProjectConnectionString"].ConnectionString;
        SqlDataAdapter da = new SqlDataAdapter("SELECT ID, PURPOSE, [START_DATE], [END_DATE], [START_TIME], [END_TIME] FROM [Schedule]", constr);
        da.SelectCommand.Parameters.AddWithValue("start", start);
        da.SelectCommand.Parameters.AddWithValue("end", start.AddDays(days));
        DataTable dt = new DataTable();
        da.Fill(dt);

        for (int i = 0; i < dt.Rows.Count; i++)
        {
            dt.Rows[i]["START_DATE"] = CombineDateAndTime(dt.Rows[i]["START_DATE"], dt.Rows[i]["START_TIME"]);
            dt.Rows[i]["END_DATE"] = CombineDateAndTime(dt.Rows[i]["END_DATE"], dt.Rows[i]["END_TIME"]);
        }

        return dt;
    }

    public static DateTime CombineDateAndTime(object date, object time)
    {
        if (date == null)
        {
            // Add some logic for this scenario. Here are 2 examples:
            //throw new ArgumentNullException("date");
            //date = DateTime.MaxValue;
        }
        if (time == null)
        {
            // Add some logic for this scenario.
            //throw new ArgumentNullException("time");
            //time = 0;
        }

        DateTime dt = Convert.ToDateTime(date);
        float hoursAndMinutes = Convert.ToInt32(time);

        return CombineDateAndTime(dt, hoursAndMinutes);
    }
    public static DateTime CombineDateAndTime(DateTime date, float time)
    {
        int hours = Convert.ToInt32(Math.Round((decimal)time / 100, MidpointRounding.AwayFromZero));
        float remainder = time - (hours * 100);
        int minutes = Convert.ToInt32(Math.Round((decimal)remainder, MidpointRounding.AwayFromZero));
        DateTime returnDate = date.Date.AddHours(hours).AddMinutes(minutes);
        return returnDate;
    }
}

Answer 1

执行此操作后:da.Fill(dt);，您可以更改它接收到的数据。

这是我认为您可以如何将 2 列组合成 1 个 DateTime 的代码片段:

public static void JustSomeMethodToTestTheConvertion()
{
    DateTime date = DateTime.Now.Date;
    float time = 900;
    DateTime concatDate = CombineDateAndTime(date, time);
    Console.WriteLine(concatDate);
}

public static DateTime CombineDateAndTime(DateTime date, float time)
{
    int hours = Convert.ToInt32(Math.Round((decimal)time / 100, MidpointRounding.AwayFromZero));
    float remainder = time - (hours * 100);
    int minutes = Convert.ToInt32(Math.Round((decimal)remainder, MidpointRounding.AwayFromZero));
    return date.AddHours(hours).AddMinutes(minutes);
}

因此您需要循环遍历您的数据并执行类似这样的操作，然后将其填入。

回复您的评论:

private DataTable dbGetEvents(DateTime start, int days)
{
    string constr = ConfigurationManager.ConnectionStrings["ProjectConnectionString"].ConnectionString;
    SqlDataAdapter da = new SqlDataAdapter("SELECT ID, PURPOSE, [START_DATE], [END_DATE] FROM [Schedule]  WHERE NOT (([END_DATE] <= @start) OR ([START_DATE] >= @end))", constr);
    da.SelectCommand.Parameters.AddWithValue("start", start);
    da.SelectCommand.Parameters.AddWithValue("end", start.AddDays(days));
    DataTable dt = new DataTable();
    da.Fill(dt);

    for (int i = 0; i < dt.Rows.Count; i++)
    {
        dt.Rows[i]["YourConcatDateColumn"] = CombineDateAndTime(dt.Rows[i]["Date"], dt.Rows[i]["Time"]);
    }

    return dt;
}

public static DateTime CombineDateAndTime(object date, object time)
{
    if (date == null)
    {
        // Add some logic for this scenario. Here are 2 examples:
        //throw new ArgumentNullException("date");
        //date = DateTime.MaxValue;
    }
    if (time == null)
    {
        // Add some logic for this scenario.
        //throw new ArgumentNullException("time");
        //time = 0;
    }

    DateTime dt = Convert.ToDateTime(date);
    float hoursAndMinutes = Convert.ToInt32(time);

    return CombineDateAndTime(dt, hoursAndMinutes);
}
public static DateTime CombineDateAndTime(DateTime date, float time)
{
    int hours = Convert.ToInt32(Math.Round((decimal)time / 100, MidpointRounding.AwayFromZero));
    float remainder = time - (hours * 100);
    int minutes = Convert.ToInt32(Math.Round((decimal)remainder, MidpointRounding.AwayFromZero));
    DateTime returnDate = date.Date.AddHours(hours).AddMinutes(minutes);
    return returnDate;
}

但您需要SELECT 所有需要的列，并将"YourConcatDateColumn" 部分更改为要使用的正确列的名称。例如。 ["START_DATE"] 和 ["START_TIME"]。