December 16, 2010 at 2:56 pm
I have a Sql Sever 2008 DB with a list of dates and events associated with those dates. I’d like to return only those events that are happening on the current date.
Something like:
SELECT [event] FROM [new database] WHERE ([date] = @currentdate) ..I know this isn’t right.
Any advice would be appreciated
December 16, 2010 at 3:11 pm
Hard to tell without knowing anything about your table structure...
Please provide more details. See the first link in my signature on how to do it to get a coded and tested answer.
December 16, 2010 at 4:21 pm
I haven't actually constructed the table but it will have three columns, with no links to any other tables. ID column with primary key int automatically adds an incremental. The next column is date the the third column is event. I can add a column called "istoday" with yes or no and construct a querry 'where istoday=yes' and manual change it everyday but I rather just look at the date of the event and present any events that match with the current date. I'll construct the table if that would be helpful but what I've listed above is what it will be.
December 16, 2010 at 4:32 pm
Does this handle it for you?
(Since you posted on a SQL 2008 forum, I gave you a SQL 2008 response. Let us know if you're not on SQL 2008 for proper code for the version that you are on.)
DECLARE @Date1 DATE = GetDate(),
@Date2 DATE = DateAdd(day, 1, GetDate());
SELECT *
FROM YourTable
WHERE YourDateField >= @Date1
AND YourDateField < @Date2;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 16, 2010 at 4:33 pm
based on your description your orignal approach isn't that far off...
SELECT [event] FROM [yourTable] WHERE [date] = @currentdate
The "tricky" part is how you store the event dates in the [date] column and how you define @currentdate. Do you plan to hold store time information in there as well or just the date?
Anyhow, I guess you'll benefit from
dateadd(dd,datediff(dd,0,getdate()),0)
(It'll return the beginning of today...)
Question aside: This sounds like homework... Is it?
December 16, 2010 at 5:01 pm
No not homework. A real honest project. I created a test table. I may not have the vocabulary to continue this string with you becasue I am a novice at sql server. Yes sql 2008 but here goes:
This is an asp.net page. This code returns all events where the date is today because the value in the "isToday" column is yes. The date is stored as datetime.
<asp:GridView id="GridView2" runat="server" AutoGenerateColumns="False" DataKeyNames="id" DataSourceID="testingeventWith">
<Columns>
<asp:boundfield DataField="id" HeaderText="id" InsertVisible="False" ReadOnly="True" SortExpression="id"></asp:boundfield>
<asp:boundfield DataField="eventDate" DataFormatString="{0:d}" HeaderText="eventDate" SortExpression="eventDate"></asp:boundfield>
<asp:boundfield DataField="eventName" HeaderText="eventName" SortExpression="eventName"></asp:boundfield>
<asp:boundfield DataField="isToday" HeaderText="isToday" SortExpression="isToday">
</asp:boundfield>
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="testingeventWith" runat="server" ConnectionString="<%$ ConnectionStrings:testingdateConnectionString %>"
SelectCommand="SELECT * FROM [testevent] WHERE ([isToday] = @isToday)">
<SelectParameters>
<asp:querystringparameter DefaultValue="yes" Name="isToday" QueryStringField="isToday" Type="String" /></SelectParameters>
</asp:SqlDataSource>
I'd like to return the events for the current date without resorting to the isToday column.
When I substitute the suggested code:
<asp:SqlDataSource ID="testeventtime" runat="server" ConnectionString="<%$ ConnectionStrings:testingdategoliathConnectionString %>" SelectCommand="SELECT [eventName], [eventDate] FROM [testevent] WHERE ([eventDate] = @currentdate)">
<SelectParameters>
<asp:querystringparameter DefaultValue="currentdate" Name="eventDate" QueryStringField="evnetDate" Type="DateTime" />
</SelectParameters>
</asp:SqlDataSource>
I get an error that says:
The string was not recognized as a valid DateTime. There is a unknown word starting at index 0
Hope this helps and I appologize if I'm not answering your questions correctly but as I noted I have limited vocabulary on this subject.
December 16, 2010 at 8:10 pm
Suggestion, instead of building your queries inside the apsx code, try encasulating the queries inside stored procedures or views and select from those. If the query has to change (but the returned result set remains the same) you only have to modify the stored procedure or view, not your application.
December 17, 2010 at 7:07 am
Thank you for the suggestion. Unfortunately at this time, I haven't explored "Stored Procedures" so as a Rookie, I don't yet have the skill.
December 17, 2010 at 7:31 am
It takes maybe 30 min to learn,
putting your sql into stored procedure not only makes you application more secure but you won't have to dive into your code if any changes occur in your table (There might be in the future)
Reconsider adding a column isToday cause its not a good idea to create a column which values have to be updated daily.
DATEDIFF(d, UtcTime, getdate()) = 0
where you replace UtcTime with the column that holds your dates should give you all the events for today.
December 19, 2010 at 7:14 am
Thank you Resender however I've not been able to figure out where this goes. Is this code meant to fit inside this querry:
<asp:SqlDataSource ID="testeventtime" runat="server" ConnectionString="<%$ ConnectionStrings:testingdategoliathConnectionString %>" SelectCommand="SELECT [eventName], [eventDate] FROM [testevent] WHERE ([eventDate] = @currentdate)">
<SelectParameters>
<asp:querystringparameter DefaultValue="currentdate" Name="eventDate" QueryStringField="eventDate" Type="DateTime" />
</SelectParameters>
</asp:SqlDataSource>
If so can you help me understand how it fits. Eliminating the "isToday" feature is the objective in this.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply