Change SelectedDate (DateTime) format

[Greg]

Hello-

I'm working on a page that has a regular calendar control, i bind "selectedDays" to a database and show these days with an offset background color.  This tells the user that a record exists on that day.  My next step is for the user to then click that date and in a RadDataGrid show all the records for that day.

At this time when i click a highlighted date that i know has a record, it will only show a matching record if it has a Date and Time stamp of 12:00:00 attached to it.  However the database has DateTime for when the record was created by the user.

Basically i want the SQL statement triggered by the SelectedDate property of the Calendar control to only send the format Month,Day,Year.  I'm not sure how i can control this.

At this time, my code is inline for the clicking of the date to populate the RadGrid.  The only codebehind is the code that displays the different background for SelectedDates with a record(s).

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:LionsConnectionString %>"
        SelectCommand="SELECT ArticleID, ArticleTitle, ArticleSubject, ArticleContent, ArticleDateTime, ArticleExp, ArticleExpChkBox, UserID, UserName FROM tblLionsArticles WHERE ((ArticleDateTime) = @ArticleDateTime)">
        <SelectParameters>
            <asp:ControlParameter ControlID="RadDatePicker1" DefaultValue="1/1/2008" Name="ArticleDateTime"
                PropertyName="SelectedDate" />
        </SelectParameters>
    </asp:SqlDataSource>
    <div id="output" class="module" style="overflow: auto; float: right; width: 256px;
        height: 240px; margin-right: 0px; color: #ff0000; padding: 5px 5px 5px 5px;">
    </div>
    <div style="padding-left: 5px">
        <telerik:RadCalendar ID="RadCalendar1" runat="server" Font-Names="Arial,Verdana,Tahoma"
            ForeColor="Black" Style="border-color: #ececec" EnableMultiSelect="False" Skin="Black"
            ViewSelectorText="x" OnDayRender="RadCalendar1_DayRender" AutoPostBack="True"
            EnableViewSelector="true" UseColumnHeadersAsSelectors="False" UseRowHeadersAsSelectors="False"
            ShowOtherMonthsDays="False">
            <ClientEvents OnDateClick="Calendar_OnDateClick2" />
            <SpecialDays>
                <telerik:RadCalendarDay IsSelected="true" Repeatable="Today" Date="" ItemStyle-CssClass="rcToday">
                    <ItemStyle CssClass="rcToday"></ItemStyle>
                </telerik:RadCalendarDay>
            </SpecialDays>
        </telerik:RadCalendar>

Thank you!

[ManniAT]

Hi,

if you use SQL2008 (and don't need the timestamp) change the column to date (new datatype).
If not - a "strange looking" convert can help to remove the time (set it to 0).
CONVERT(datetime, CONVERT(varchar(8), ArticleDateTime, 112))
This converts the DateTime to an ISO string 20090322 and than back to a datetime.

The calendar should give you the day - and the convert will strip the times from your record.
I'm not sure what you ment with 12:00 - but I hope the "convert trick" helps.

Regards

Manfred

[Greg]

Thank  you.   The change from DateTime to Date in SQL 2008 database did work.  However, I would prefer to use the other method.  The reason, I have an admin interface where a user can type in a new article, when they write the article to the database the ArticleDateTime field runs the SQL getdate() funtion this includes the Date and Time stamp.  This way if multiple users enter in articles for the same day they can be sorted down to the time.

If i use the first method of just Date in SQL then i loose this ability unless i create a new column and modify all of my pages so that one shows one way  and another page binds do a different value.  I would prefer to keep only one column and do manipulation....keep it simple.

Could you explain where i might put the CONVERT statement you are referencing?  Do i add that to my inline SQL Select statement, shown in my code block from the first post?

Let me know which method you think is best.

Thanks a TON!
Greg

[Greg]

I think the other part to mention is that there are two things to keep in mind.

1. The input from the Calendar Control which become the WHERE in my SQL Statement
1. Running my SQL statement
2. And outputing the date on the page (through RadGrid control)

I need to keep straight all of the inputs and outputs and make sure the formats are what i expect.

So, when i click a date on the calendar control.  Which is @ArticleDateTime in the WHERE clause.  This needs to be sent in the Date format (no time).

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:LionsConnectionString %>"
        SelectCommand="SELECT ArticleID, ArticleTitle, ArticleSubject, ArticleContent, ArticleDateTime, ArticleExp, ArticleExpChkBox, UserID, UserName FROM tblLionsArticles WHERE ((ArticleDateTime) = @ArticleDateTime)">
        <SelectParameters>
            <asp:ControlParameter ControlID="RadDatePicker1" DefaultValue="1/1/2008" Name="ArticleDateTime"
                PropertyName="SelectedDate" />
        </SelectParameters>
    </asp:SqlDataSource>

I need to somehow accept only the Date from the calendar parameter, as well as the database understand only the date even though the SQL database has DateTime.

I having a hard time understanding how to accept only Date and SQL do a SELECT only for Date and then return only the Date value.

I hope this helps a little more.

Thanks,
Greg

[ManniAT]

Hi,

SQL Client handle DateTime values - and having such values means always messing around with the time part of that guy.
Select ..... Where convert(date,ArticleDateTime) = @Article... will do the job.
And correct me if I'm wrong - the calendar returns a time of 0 - it is "SelectedDATE!!"

So the outputs are datetimes - but the timepart is reduced to 0 by default on the calendar and by the convert in the select statement.

This change in the query should do the job - it selects all Records from SelectedDate 00:00:00 to Selected Date 23:59:59

Cheers

Manfred