CODEWITHSUNDEEP
sqlsql-servert-sqldate
user700792
user700792

Reputation: 2249

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

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

Upvotes: 4

Views: 26918

Answers (6)

prabu
prabu

Reputation: 21

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

Upvotes: 2

elvis
elvis

Reputation: 322

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 convert allows for some formatting options. In my example, I simply trimmed off the time from the date.

Upvotes: 0

Bhairab
Bhairab

Reputation: 51

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'

Upvotes: 5

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

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

If you want it for 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)

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)

Query

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

Upvotes: 0

BlueMonkMN
BlueMonkMN

Reputation: 25601

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]

Upvotes: 1

abatishchev
abatishchev

Reputation: 100366

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

Upvotes: 5

Related Questions