我怎么能转换成一个.net日期时间为T-SQL日期时间 [英] How can I convert a .Net Datetime to a T-SQL Datetime

问题描述

  MyDataSource.SelectParameters [的startDate。默认值=
                                     fromDate.SelectedDate.Date.ToString（）;
 

正如你可以看到我设置参数的默认值在我的.Net的SqlDataSource。我不认为该字符串输出格式正确，但。什么是正确的格式，所以T-SQL日期时间将正确的搭配呢？

下面是被输出，现在的格式：

• 在2009年5月1日上午12:00:00
• 在2009年5月4日下午11:59:59

编辑：之前，我有数据源，它应该已被写入的SqlDataSource

下面是一些code

 ＆LT; ASP：SqlDataSource的ID =MyDataSource=服务器的ConnectionString =＆LT;％$的ConnectionStrings：SAMPLEDB％＆gt;中的SelectCommand =Recording_SelectSelectCommandType =StoredProcedure的OnSelecting =MyDataSource_Selecting＆GT;
    ＆LT; SelectParameters＆GT;
        ＆LT; ASP：ControlParameter控件ID =respondentID默认值=％NAME =RespondentID属性名=文本类型=字符串/＆GT;
        ＆LT; ASP：SessionParameter名称=项目codeSessionField =凸出类型=字符串/＆GT;
        ＆LT; ASP：ControlParameter控件ID =interviewerListNAME =InterviewerID属性名=的SelectedValue类型=字符串/＆GT;
        ＆LT; ASP：ControlParameter控件ID =DISP $ C $栏列表NAME =DISP code属性名=的SelectedValue类型=的Int32/＆GT;
        ＆LT; ASP：ControlParameter控件ID =没有fromdate默认值=1/1/1900NAME =的startDate属性名=的SelectedValueTYPE =日期时间/＆GT;
        ＆LT; ASP：ControlParameter控件ID =TODATE默认值=1/1/1900NAME =结束日期属性名=的SelectedValueTYPE =日期时间/＆GT;
    ＆LT; / SelectParameters＆GT;
＆LT; / ASP：SqlDataSource的＆GT;
 

编辑： 好吧，我想通了这个问题，它似乎有点傻。在code从未调用默认值，因为总有一个价值present。所以，我唯一的解决办法是使一个新的隐藏的标签控制和负载校正日期时间到了。这pretty的多的伎俩。我不知道我怎么忽略了。

解决方案

你是如何开始创建参数？你应该能够在那个时候指定的数据类型为日期时间。然后分配您的日期时间的参数直接而不是试图将其转换为字符串。

---

根据您发布的code，最好的选择是可能只是pre-填充你的控制。此外，看起来罚款在这方面的默认值，所以我怀疑的问题是其他地方。尝试将控制为False的 CancelSelectOnNullParameter 属性。

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?

Here are the formats that are outputted right now:

• 5/1/2009 12:00:00 AM
• 5/4/2009 11:59:59 PM

Edit: Before I had DataSource where it should have been written 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.

解决方案

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.

---

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.

这篇关于我怎么能转换成一个.net日期时间为T-SQL日期时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！