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

Question

Hello,

I am using a ASP Calendar control to insert a datetime value into a field to be part of an insert 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>

Answer 1

Hey,

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.

Answer 2

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

Answer 3

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.]