ASP.NET Calendar control returns Date type but I need Datetime to insert into SQL Server 2005

I am using a ASP Calendar control to insert a datetime value into a field to be part of an insert开发者_StackOverflow社区 to a SQL Server 2005 db table.

I get the following error when i submit the form to server and try to insert into table:

ArgumentException: The version of SQL Server in use does not support datatype 'date'.

Seems like Calendar control returns a Date type value. How could i make the Calendar control return a DateTime value instead?

I know now that SQL Server 2005 does not support Date type fields.

Here is my client code where I have my Calendar control in a FormView:

<tr>
    <td class="style4">
        Date</td>
    <td>
        <asp:Calendar ID="Calendar1" runat="server" BackColor="White" 
            BorderColor="#3366CC" BorderWidth="1px" CellPadding="1" 
            DayNameFormat="Shortest" Font-Names="Verdana" Font-Size="8pt" 
            ForeColor="#003399" Height="200px" SelectedDate='<%# Bind("EventDate") %>' 
            Width="220px">
            <SelectedDayStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />
            <SelectorStyle BackColor="#99CCCC" ForeColor="#336666" />
            <WeekendDayStyle BackColor="#CCCCFF" />
            <TodayDayStyle BackColor="#99CCCC" ForeColor="White" />
            <OtherMonthDayStyle ForeColor="#999999" />
            <NextPrevStyle Font-Size="8pt" ForeColor="#CCCCFF" />
            <DayHeaderStyle BackColor="#99CCCC" ForeColor="#336666" Height="1px" />
            <TitleStyle BackColor="#003399" BorderColor="#3366CC" BorderWidth="1px" 
                Font-Bold="True" Font-Size="10pt" ForeColor="#CCCCFF" Height="25px" />
        </asp:Calendar>
    </td>
</tr>

As far as I could understand, you trying to create a table with column of type DATE. Indeed, only SQL Server 2008 support it, 2005 - don't.

Even if Calendar.SelectedDate return System.DateTime and you will try to insert it into a column of type DATETIME (on SQL Server 2005) it will be inserted successfully.

If column will have type DATE (on SQL Server 2008) the date will be truncated and also inserted successfully

Here is my code:

<asp:SqlDataSource ID="SqlDataSource2" runat="server" 
    ConnectionString="<%$ ConnectionStrings:revi_rafaDbConnectionString %>" 
    DeleteCommand="DELETE FROM [Event] WHERE [EventID] = @EventID" 
    InsertCommand="INSERT INTO [Event] ([[HostLogin], [EventDate], [EventTime], [EventLocation], [EventDescription]) VALUES (@LoginID, @EventDate, @EventTime, @EventLocation, @EventDescription)" 
    SelectCommand="SELECT * FROM [Event]" 
    UpdateCommand="UPDATE [Event] SET [HostLogin] = @HostLogin, [EventDate] = @EventDate, [EventTime] = @EventTime, [EventLocation] = @EventLocation, [EventDescription] = @EventDescription WHERE [EventID] = @EventID">
    <DeleteParameters>
        <asp:Parameter Name="EventID" Type="Int32" />
    </DeleteParameters>
    <UpdateParameters>
        <asp:Parameter Name="HostLogin" Type="String" />
        <asp:Parameter DbType="Datetime" Name="EventDate" />
        <asp:Parameter Name="EventTime" Type="String" />
        <asp:Parameter Name="EventLocation" Type="String" />
        <asp:Parameter Name="EventDescription" Type="String" />
        <asp:Parameter Name="EventID" Type="Int32" />
    </UpdateParameters>
    <InsertParameters>
        <asp:Parameter Name="LoginID" />
        <asp:Parameter DbType="Datetime" Name="EventDate" />
        <asp:Parameter Name="EventTime" Type="String" />
        <asp:Parameter Name="EventLocation" Type="String" />
        <asp:Parameter Name="EventDescription" Type="String" />
    </InsertParameters>
</asp:SqlDataSource>

I've now gotten past the date into datetime issue. I set the DbType for EventDate to Datetime. However, I now get:

[SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.]

Not sure why you are getting this? Are you talking ASP.NET 2.0 or greater? By default, it uses a DateTime object; you could always ensure this by doing:

DateTime evalDate = new DateTime(cal1.SelectedDate.Year, cal1.SelectedDate.Month, cal1.SelectedDate.Day, 0, 0, 0);

And use this to pass to the DB...

Could this also be due to an insert proc that has a date and not a datetime variable too?

EDIT: A DateTime evaluates to 1/1/0001 when not supplied, which is an issue for SQL Server; you have to ensure it isn't less than the 1/1/1753. That is a pain... if you can make the field null and use a DateTime? or Nullable, that is a workaround, or always ensure a minimum date.

I belive it does not return the correct format because it switches around the months and years. There is a difference between the calender return, datetime, and datetime2. You may want to do datetime2 as it holds any possibly values the .net can return, but you need to do your homework on this.

There are many solutions to getting your date from the calender but the one I prefer is the following:

string calClause = " START_TS > '";

calClause += Calendar1.SelectedDate.ToString("yyyy/MM/dd") + "' ";

This code snippet should generate something that looks like this:

START_TS > '2013-09-04'  

You must then add the where

WHERE START_TS > '2013-09-04'  

And then add this to your command text after the where clause. (like my application you may want to include blank and null values in your table)

SELECT * From YourTableName WHERE START_TS > '2013-09-04 ' AND START_TS != '' AND START_TS is not null 

I use a method to generate the SQL command usually written as cmdText based on various on page items such as checkboxes and calenders.