CODEWITHSUNDEEP
sqlsql-serverdate
user2684131
user2684131

Reputation: 147

only retrieve record from sql database which have todays's date

I'm using SQL SERVER 2008 R2 to store data and C# web application to retrieve data and display it into gridview.

I have already stored data with different dates Now I want to show data daily on C# gridview according to date like : as today is 15 august and data having date as 15 august only populate the gridview.

I have created the datasource to populate the gridview which previously shows all the data irrespective of date: code is as follows:

 <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:LoginConnectionString %>" 
        SelectCommand="SELECT * FROM [Student]"></asp:SqlDataSource>

Now I want to just show only current date/todays date data as explained above. Please guide me how to write the sql command to achieve the above functionality. Table name is Student Column Name is date stored as datetime

Thanks

Upvotes: 0

Views: 4163

Answers (4)

sealz
sealz

Reputation: 5408

You can use DATEDIFF

SELECT * FROM tablename where datefield = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

EDIT

I can not check at the moment but you can adjust and pull all dates between dates with a query like the one below.

SELECT * FROM TEST_NAMES 
where Date < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1)
and
Date >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

EDIT
Got around to testing. You can account for the timestamp differences you can convert the field to a VARCHAR and use the below query.

SELECT * FROM tablenames 
where CONVERT(VARCHAR(10), Datecolumn) = CONVERT(VARCHAR(10), GETDATE())

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280262

Better than casting the column as a date, IMHO, would be to use an open-ended range:

DECLARE @today DATE = GETDATE();

SELECT 
...
WHERE column >= @today AND column < DATEADD(DAY, 1, @today);

Some background info on why you don't want to convert to string and why, in most cases, you want to avoid applying functions to columns.

Upvotes: 0

ganders
ganders

Reputation: 7433

Add your where clause to your select statement like this:

Where Cast(Student.Date As Date) = Cast(GetDate() As Date)

Edit: updated to use the cast instead of convert, and made it so it's only equal to today's date.

Upvotes: 1

Dan
Dan

Reputation: 10680

Assuming that "date" also contains time of day information, you have to strip it when comparing to current date:

WHERE CAST(Student.[Date] AS DATE) = CAST(GETDATE() AS DATE)

Upvotes: 2

Related Questions