SQL query

[Lakshith]

In my database I have a field date and eventname.I want to retieive eventnames after the current date.How can I do it?

[sknake]

Just the select query:

Select *
From #Table
Where [Date] >= Cast(Floor(Cast(GetDate() as float)) as DateTime)

A full example:

IF OBJECT_ID('tempdb..#Table', 'U') IS NOT NULL DROP TABLE #Table
Create Table #Table
(
  [Date] DateTime,
  EventName varchar(100)
)

Insert Into #Table ([Date], EventName) Values (GetDate()-3, 'Event 1')
Insert Into #Table ([Date], EventName) Values (GetDate()-2, 'Event 2')
Insert Into #Table ([Date], EventName) Values (GetDate()-1, 'Event 3')
Insert Into #Table ([Date], EventName) Values (GetDate(), 'Event 4')
Insert Into #Table ([Date], EventName) Values (GetDate()+1, 'Event 5')
Insert Into #Table ([Date], EventName) Values (GetDate()+2, 'Event 6')
Insert Into #Table ([Date], EventName) Values (GetDate()+3, 'Event 7')

Select *
From #Table
Where [Date] >= Cast(Floor(Cast(GetDate() as float)) as DateTime)

And since you're asking in the C# forum:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Windows.Forms;

namespace daniweb
{
  public partial class frmDateSelect : Form
  {
    public frmDateSelect()
    {
      InitializeComponent();
    }

    /// <summary>
    /// Concatenates the query string
    /// </summary>
    /// <param name="Sql"></param>
    /// <returns></returns>
    private static string GetText(List<string> Sql)
    {
      StringBuilder sb = new StringBuilder();
      for (int i1 = 0; i1 < Sql.Count; i1++)
        sb.AppendLine(Sql[i1]);
      return sb.ToString();
    }

    /// <summary>
    /// Builds a connection string
    /// </summary>
    /// <param name="server"></param>
    /// <param name="database"></param>
    /// <returns></returns>
    internal static string BuildSqlNativeConnStr(string server, string database)
    {
      return string.Format("Data Source={0};Initial Catalog={1};Integrated Security=True;", server, database);
    }

    /// <summary>
    /// Gets data after today using logic to determine what today is
    /// </summary>
    /// <returns></returns>
    internal static DataTable GetTable()
    {
      DataTable result = default(DataTable);

      List<string> Sql = new List<string>();
      Sql.Add("IF OBJECT_ID('tempdb..#Table', 'U') IS NOT NULL DROP TABLE #Table");
      Sql.Add("Create Table #Table");
      Sql.Add("(");
      Sql.Add("  [Date] DateTime,");
      Sql.Add("  EventName varchar(100)");
      Sql.Add(")");
      Sql.Add("");
      Sql.Add("Insert Into #Table ([Date], EventName) Values (GetDate()-3, 'Event 1')");
      Sql.Add("Insert Into #Table ([Date], EventName) Values (GetDate()-2, 'Event 2')");
      Sql.Add("Insert Into #Table ([Date], EventName) Values (GetDate()-1, 'Event 3')");
      Sql.Add("Insert Into #Table ([Date], EventName) Values (GetDate(), 'Event 4')");
      Sql.Add("Insert Into #Table ([Date], EventName) Values (GetDate()+1, 'Event 5')");
      Sql.Add("Insert Into #Table ([Date], EventName) Values (GetDate()+2, 'Event 6')");
      Sql.Add("Insert Into #Table ([Date], EventName) Values (GetDate()+3, 'Event 7')");
      Sql.Add("");
      Sql.Add("Select *");
      Sql.Add("From #Table");
      Sql.Add("Where [Date] >= Cast(Floor(Cast(GetDate() as float)) as DateTime)");
      string query = GetText(Sql);
      string connStr = BuildSqlNativeConnStr("apex2006sql", "Scott");
      using (SqlConnection conn = new SqlConnection(connStr))
      {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
          using (SqlDataReader dr = cmd.ExecuteReader())
          {
            result = new DataTable();
            result.Load(dr);
          }
        }
        conn.Close();
      }
      return result;
    }

    /// <summary>
    /// Gets data after a certain date provided in code
    /// </summary>
    /// <param name="dt"></param>
    /// <returns></returns>
    internal static DataTable GetTable(DateTime dt)
    {
      //It will blow up if it hits the SQL server so just set it to the max boundary.
      if (dt > System.Data.SqlTypes.SqlDateTime.MaxValue.Value)
        dt = System.Data.SqlTypes.SqlDateTime.MaxValue.Value;
      else if (dt < System.Data.SqlTypes.SqlDateTime.MinValue.Value)
        dt = System.Data.SqlTypes.SqlDateTime.MinValue.Value;

      DataTable result = default(DataTable);

      List<string> Sql = new List<string>();
      Sql.Add("IF OBJECT_ID('tempdb..#Table', 'U') IS NOT NULL DROP TABLE #Table");
      Sql.Add("Create Table #Table");
      Sql.Add("(");
      Sql.Add("  [Date] DateTime,");
      Sql.Add("  EventName varchar(100)");
      Sql.Add(")");
      Sql.Add("");
      Sql.Add("Insert Into #Table ([Date], EventName) Values (GetDate()-3, 'Event 1')");
      Sql.Add("Insert Into #Table ([Date], EventName) Values (GetDate()-2, 'Event 2')");
      Sql.Add("Insert Into #Table ([Date], EventName) Values (GetDate()-1, 'Event 3')");
      Sql.Add("Insert Into #Table ([Date], EventName) Values (GetDate(), 'Event 4')");
      Sql.Add("Insert Into #Table ([Date], EventName) Values (GetDate()+1, 'Event 5')");
      Sql.Add("Insert Into #Table ([Date], EventName) Values (GetDate()+2, 'Event 6')");
      Sql.Add("Insert Into #Table ([Date], EventName) Values (GetDate()+3, 'Event 7')");
      Sql.Add("");
      Sql.Add("Select *");
      Sql.Add("From #Table");
      //Sql.Add("Where [Date] >= Cast(Floor(Cast(GetDate() as float)) as DateTime)");
      Sql.Add("Where [Date] >= @Date");
      string query = GetText(Sql);
      string connStr = BuildSqlNativeConnStr("apex2006sql", "Scott");
      using (SqlConnection conn = new SqlConnection(connStr))
      {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
          cmd.Parameters.Add(new SqlParameter("@Date", SqlDbType.DateTime)).Value = dt;
          using (SqlDataReader dr = cmd.ExecuteReader())
          {
            result = new DataTable();
            result.Load(dr);
          }
        }
        conn.Close();
      }
      return result;
    }

    private void button1_Click(object sender, EventArgs e)
    {
      using (DataTable dt = GetTable(), dt2 = GetTable(DateTime.Today))
      {
        MessageBox.Show(string.Format("You have {0:F0} and {1:F0} records respectively", dt.Rows.Count, dt2.Rows.Count));
      }
    }

  }
}

I did copy and paste the GetDate() method and only changed ~2 lines of code. In a production environment you would probably want to use a single method.