how i can retrieve rows by specific date in sql server?

Question

I'm working in project for by PHP and SQL Server.

The owner want from me design page show only users who register in same day

i.e., if today 11-3-2011 i want show only all users who register in 11-3-2011

The table is:

id  username  date

1   john      11\3\2011
2   sara      11\3\2011
3   john      5\1\2011
4   kreem     1\2\2011

i make it by mysql

where DATE_ADD( items.created_date, INTERVAL 1 DAY ) > NOW() 

this cable of code show data which only insert in same day thats mean if today 10-4-2011 it will show only data which insert in 10-4-2011 if i today 15-4-2011 and im dose not insert any thing it will not show any thing, how i can build code like this in sql server? hope to be my question clear and understand

Solution 1

select id, userName
from YourTable
where CAST(date AS DATE) = CAST(GETDATE() AS DATE)

Function GETDATE() returns the current date and time.

CAST(column as TYPE) will threat DateTime as just Date to omit differences in Time

Solution 2

For getting today's date record:

select * from tablename where date column between '2011-10-25 00:00:00' And '2011-10-25 23:59:59'

Solution 3

select * from yourtable where date = CONVERT(VARCHAR(20),GETDATE(),101) // hope this will be helpful to get current date value..

Solution 4

This query compares only the date piece of today, ignoring the time. It works in MS SQL Server, I'm not sure about other implimentations:

select *
from YourTable
where convert(datetime, floor(convert(float, GETDATE()))) = [date]

View more solutions

Comments

[Marco]

@Mikael Eriksson: I think he wants to have a function working every day, not only for the specified day...

[abatishchev]

What for so much operations when you can just CAST(GETDATE() as Date) to get only date-part and omit time-part

[Mikael Eriksson]

This will work in SQL Server 2008 or later. And I guess that CAST(date AS DATE) will make an index on date useless, if it's not already of data type DATE but then the cast is unnecessary.

[Bobby Adams]

I guess I learned a bad practice from leftlobed.wordpress.com/2008/05/03/… and stillnetstudios.com/comparing-dates-without-times-in-sql-ser‌​ver

[abatishchev]

I see there are a lot of variants

[abatishchev]

@Mikael: Will index on calculated value make sense in this case?

[Bobby Adams]

After further review, I suspect your version only works in SQL 2008, and many applications want to be compatible with SQL Server 2005 still.

[Martin Smith]

@Mikael - Casting on Date is sargable. See connect.microsoft.com/SQLServer/feedback/details/526431/… Obviously impossible to tell how suitable this is for the OP without additional information.

[Mikael Eriksson]

@Martin – That is great, makes life easier when dealing with datetime. @abatishchev – It is not necessary with index on calculated value. I did not know that casting datetime to date is sargable.

[user700792]

yes Mikael Eriksson i need function run automatic like say before

[user700792]

what is [date]? is that function and what is d after ( did you mean column name?

[Mikael Eriksson]

@user – [Date] is a field in YourTable called Date. Since Date is also a data type you can use the brackets to not have any doubts to what you mean. The d is a parameter to dateadd and datediff specifying that the functions should work with days.

[user700792]

sorry, i cant get it........lets me tell i have table his name users and column which include dates his name user_register_date should i do like this => select * from users where user_register_date >= dateadd(d, datediff(d, 0, getdate())-9, 0) ? please fix it if i have wrong

[Mikael Eriksson]

@user – Your query is correct. I have updated the answer with your query and some sample data.