Having trouble with calendar dates.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Having trouble with calendar dates.

Author

Topic

AJCUSMC
Starting Member

1 Post

Posted - 2011-09-30 : 20:56:16

What I am wanting to do, ultimately, is be able to select a range between two dates, have info pulled from a db upon selection of said dates, and forward to the results. I have 4 different variables to select the db information from, date created, date certified, and date reviewed, and date completed. For some reason, only date created works, neither of the other two succeed. What am I doing wrong?

<form id="form1" runat="server">
<hr />
<table>
<tr>
<td>
<asp:DropDownList ID="dpquery" runat="server">
<asp:ListItem>DATE_CREATED</asp:ListItem>
<asp:ListItem>REVIEWED_DATE</asp:ListItem>
<asp:ListItem>DATE_CERTIFIED</asp:ListItem>
<asp:ListItem>COMPLETION_DATE</asp:ListItem>
</asp:DropDownList>
</td>
<td>
</td>
</tr>
<tr>
<td>
<asp:Label ID="Label1" runat="server" Text="From:"></asp:Label>
</td>
<td>
</td>
<td>
<asp:Label ID="Label2" runat="server" Text="To:"></asp:Label>
</td>
</tr>
<tr>
<td>
<asp:Calendar ID="Calendar1" runat="server"
onselectionchanged="Calendar1_SelectionChanged" BackColor="#FFFFCC"
BorderColor="#FFCC66" BorderWidth="1px" DayNameFormat="Shortest"
Font-Names="Verdana" Font-Size="8pt" ForeColor="#663399" Height="129px"
ShowGridLines="True" Width="133px">
<SelectedDayStyle BackColor="#CCCCFF" Font-Bold="True" />
<SelectorStyle BackColor="#FFCC66" />
<TodayDayStyle BackColor="#FFCC66" ForeColor="White" />
<OtherMonthDayStyle ForeColor="#CC9966" />
<NextPrevStyle Font-Size="9pt" ForeColor="#FFFFCC" />
<DayHeaderStyle BackColor="#FFCC66" Font-Bold="True" Height="1px" />
<TitleStyle BackColor="#990000" Font-Bold="True" Font-Size="9pt"
ForeColor="#FFFFCC" />
</asp:Calendar>
</td>
<td class="style1">
</td>
<td>
<asp:Calendar ID="Calendar2" runat="server"
onselectionchanged="Calendar2_SelectionChanged" BackColor="#FFFFCC"
BorderColor="#FFCC66" BorderWidth="1px" DayNameFormat="Shortest"
Font-Names="Verdana" Font-Size="8pt" ForeColor="#663399" Height="129px"
ShowGridLines="True" Width="133px">
<SelectedDayStyle BackColor="#CCCCFF" Font-Bold="True" />
<SelectorStyle BackColor="#FFCC66" />
<TodayDayStyle BackColor="#FFCC66" ForeColor="White" />
<OtherMonthDayStyle ForeColor="#CC9966" />
<NextPrevStyle Font-Size="9pt" ForeColor="#FFFFCC" />
<DayHeaderStyle BackColor="#FFCC66" Font-Bold="True" Height="1px" />
<TitleStyle BackColor="#990000" Font-Bold="True" Font-Size="9pt"
ForeColor="#FFFFCC" />
</asp:Calendar>
</td>
<tr>
<td>
<asp:TextBox ID="TxtFrom" runat="server"></asp:TextBox>
</td>
<td>
</td>
<td>
<asp:TextBox ID="TxtTo" runat="server"></asp:TextBox>

</td>
<td>
<asp:Button ID="Button1" runat="server" Text="Submit" onclick="Button1_Click" />
</td>
</tr>
</tr>
</table>
<asp:TextBox ID="Txtid" runat="server" Visible="false" Text=""></asp:TextBox>
<asp:DataGrid ID="dgresults" runat="server" Font-Size="Smaller" Width="80%" OnItemCommand="dgresults_Command" >
<HeaderStyle Font-Size="Smaller" BackColor="Red" Font-Bold="true" ForeColor="White" />
<AlternatingItemStyle BackColor="#CCCCCC" />
<Columns>
<asp:TemplateColumn>
<HeaderTemplate>SELECT</HeaderTemplate>
<ItemTemplate>
<asp:ImageButton ID="Imgbtn" runat="server" ImageUrl='<%# GetStatusPicture(Container.DataItem)%>' CommandName="StatusClick" CommandArgument='<%# Eval("DOC_ID") %>' PostBackUrl="~/ReviewQuad.aspx" />
</ItemTemplate>
</asp:TemplateColumn>
</Columns>
</asp:DataGrid>
</form>

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2011-10-01 : 02:34:34

you can use stored_procedure for selecting data between some dates. for more information go to: http://forums.asp.net/ since you are using ASP technology.

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-10-01 : 06:30:41

(CONVERT(VARCHAR(10),[date created],101)>=CONVERT(VARCHAR(10),'10/03/2011',101) AND CONVERT(VARCHAR(10),[date created],101)<=CONVERT(VARCHAR(10),'10/08/2011',101))

write same condition for rest of the mentioned fields concat with the AND logical operator
1)date certified
2)date reviewed
3)date completed

10/03/2011 - text from "from textbox"
10/08/2011 - text from "To textbox"

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-01 : 13:57:58

quote:
Originally posted by jassi.singh

(CONVERT(VARCHAR(10),[date created],101)>=CONVERT(VARCHAR(10),'10/03/2011',101) AND CONVERT(VARCHAR(10),[date created],101)<=CONVERT(VARCHAR(10),'10/08/2011',101))

write same condition for rest of the mentioned fields concat with the AND logical operator
1)date certified
2)date reviewed
3)date completed

10/03/2011 - text from "from textbox"
10/08/2011 - text from "To textbox"

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

why converting to varchar? please treat dates as date type itself. no need to convert. just do like

[date created]>='20111003'
AND [date created]<='20111008'

also make sure you always pass date values in YYYYMMDD format to avoid ambiguity

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources