如何将.Net日期时间转换为T-SQL日期时间

MyDataSource.SelectParameters["startDate"].DefaultValue =
                                     fromDate.SelectedDate.Date.ToString();

As you can see I am setting the default value of a parameter in my .Net SqlDataSource. I don't think the string is outputting the correct format though. What is the correct format so a T-SQL Datetime will match it properly?

如您所见,我在.Net SqlDataSource中设置参数的默认值。我不认为字符串输出正确的格式。什么是正确的格式,以便T-SQL Datetime能够正确匹配它?

Here are the formats that are outputted right now:

以下是现在输出的格式:

5/1/2009 12:00:00 AM

5/1/2009 12:00:00 AM

5/4/2009 11:59:59 PM

5/4/2009 11:59:59 PM

Edit: Before I had DataSource where it should have been written SqlDataSource

编辑:在我有DataSource之前应该编写SqlDataSource

And here is some code

这是一些代码

<asp:SqlDataSource ID="MyDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:sampledb %>" SelectCommand="Recording_Select" SelectCommandType="StoredProcedure" OnSelecting="MyDataSource_Selecting">
    <SelectParameters>
        <asp:ControlParameter ControlID="respondentID" DefaultValue="%" Name="RespondentID" PropertyName="Text" Type="String" />
        <asp:SessionParameter Name="projectCode" SessionField="proj" Type="String" />
        <asp:ControlParameter ControlID="interviewerList" Name="InterviewerID" PropertyName="SelectedValue" Type="String" />
        <asp:ControlParameter ControlID="dispCodeList" Name="dispCode" PropertyName="SelectedValue" Type="Int32" />
        <asp:ControlParameter ControlID="fromDate" DefaultValue="1/1/1900" Name="startDate" PropertyName="SelectedValue" Type="DateTime" />
        <asp:ControlParameter ControlID="toDate" DefaultValue="1/1/1900" Name="endDate" PropertyName="SelectedValue" Type="DateTime" />
    </SelectParameters>
</asp:SqlDataSource>

Edit: Well, I figured out the problem and it will seem a bit silly. The code was never calling the DefaultValue because there was always a value present. So my only solution was to make a new hidden label control and load the corrected datetime into that. That pretty much did the trick. I don't know how I overlooked that.

编辑:嗯,我发现问题,看起来有点傻。代码永远不会调用DefaultValue,因为总有一个值存在。因此,我唯一的解决方案是创建一个新的隐藏标签控件并将更正的日期时间加载到其中。这几乎就是诀窍。我不知道我是如何忽视这一点的。

5 个解决方案

#1

How are you initially creating the parameter? You should be able to specify the datatype as datetime at that time. Then assign your datetime to the parameter directly rather than trying to convert it to a string.

你最初是如何创建参数的?您应该能够在当时将数据类型指定为datetime。然后直接将日期时间分配给参数,而不是尝试将其转换为字符串。

Based on the code you posted, your best option is probably just to pre-populate your control. Additionally, that looks fine for the default value in that context, so I suspect the problem is somewhere else. Try setting the CancelSelectOnNullParameter property of the control to False.

根据您发布的代码,您最好的选择可能只是预先填充您的控件。另外,对于该上下文中的默认值看起来很好,所以我怀疑问题出在其他地方。尝试将控件的CancelSelectOnNullParameter属性设置为False。

#2

There's a SqlDateTime struct you can use for this

你可以使用一个SqlDateTime结构

DateTime DotNetDateTime = new DateTime(2009, 5, 4);
System.Data.SqlTypes.SqlDateTime sqlDateTime = new System.Data.SqlTypes.SqlDateTime(DotNetDateTime);

Or directly in one line (as the SqlDateTime takes the same args in one of the constructor overloads as the .NET DateTime class):

或者直接在一行中(因为SqlDateTime在.NET DateTime类中的构造函数重载中采用相同的args):

System.Data.SqlTypes.SqlDateTime sqlDateTime = new System.Data.SqlTypes.SqlDateTime(2009, 5, 4);

#3

I would use this:

我会用这个:

MyDataSource.SelectParameters["startDate"].DefaultValue =
                                     fromDate.SelectedDate.Date.ToString("yyyyMMdd HH:mm:ss");

#4

This should do the trick:

这应该是诀窍:

MyDataSource.SelectParameters["startDate"].DefaultValue = fromDate.SelectedDate.Date.ToString("yyyy-MM-dd HH:mm:ss")

#5

I always force my dates to MMM dd yyyy when passing as paramters, that way there is no way it can gets messed up and flipped around.

当我作为参数传递时,我总是强迫我的约会给MMM dd yyyy,这样就没有办法让它变得混乱和翻转。

#1