display a time based event

  • 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

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



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

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

  • 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


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • 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?



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

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

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

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

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

    How to call SQL Server stored procedures from ASP

  • 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