How can I get rows by a specific date in sql server?

Question

I am working on a project for PHP and SQL Server.

The owner wants only users who register on the same day to appear on my design page

i.e. if today 11-3-2011 I want to show only all users who register on 11-3-2011

Table:

id username date 1 john 11\3\2011 2 sara 11\3\2011 3 john 5\1\2011 4 kreem 1\2\2011 

I am doing this with mysql

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

this code cable shows the data that is inserted only on the same day, which means that today 10-4-2011 it will only show the data that is inserted on 10-4-2011, if I am today 15-4-2011, and im the dose no thing is inserted that won't show anything, how can I create such code on sql server? hope my question is clear and understand

Answer 1

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

The GETDATE () function returns the current date and time.

CAST (column as TYPE) will threaten DateTime as a date to omit time differences

Answer 2

To get today's entry:

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

Answer 3

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

Answer 4

This query only compares the date of the day, ignoring the time. It works in MS SQL Server, I am not sure about other options:

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

Answer 5

 select * from YourTable where [date] >= '20110311' and [date] < '20110312' 

If you want it today (always), you can use this

 select * from YourTable where [date] >= dateadd(d, datediff(d, 0, getdate()), 0) and [date] < dateadd(d, datediff(d, 0, getdate())+1, 0) 

The last 10 days.

 select * from YourTable where [date] >= dateadd(d, datediff(d, 0, getdate())-9, 0) 

Edit 1 Query with sample data and result

Table definition

 create table users (name varchar(20), user_register_date datetime) 

Data

 insert into users values ('user today', getdate()) insert into users values ('user yesterday', getdate()-1) insert into users values ('user 9 days ago', getdate()-9) insert into users values ('user 10 days ago', getdate()-10) 

Request

 select * from users where user_register_date >= dateadd(d, datediff(d, 0, getdate())-9, 0) 

Result

 name user_register_date -------------------- ----------------------- user today 2011-04-14 08:29:28.407 user yesterday 2011-04-13 08:29:28.410 user 9 days ago 2011-04-05 08:29:28.410 

Answer 6

 SELECT * from YourTable WHERE cast(convert(varchar(10),yourdate,101) as datetime) = cast(convert(varchar(10),[any day you want],101) as datetime) 

Hope this helps. Just replace [any day you want] with a date or datetime value

The CAST function is equivalent to the CONVERT function, except that convert allows you to use some formatting options. In my example, I just cut back on time from the day.