Reputation: 2249
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: 26914
Reputation: 21
select * from yourtable where date = CONVERT(VARCHAR(20),GETDATE(),101)
// hope this will be helpful to get current date value..
Upvotes: 2
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
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
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
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
Reputation: 100358
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