How can I convert a .Net Datetime to a T-SQL Datetime

Question

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.

Answer 1

There's a SqlDateTime struct you can use for this

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):

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

Answer 2

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.

Answer 3

I would use this:

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

Answer 4

This should do the trick:

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