date query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

date query

Author

Topic

mkh
Starting Member

18 Posts

Posted - 2004-08-22 : 03:43:53

I need to use Datediff function to find all entries from the last 30 days but my datatype is datetime.
How can I do this using ONLY simgle select statement.

Can anyone guide.
regards,
mkh.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-22 : 04:36:17

select dateadd( day, -30, getdate() )

/rockmoose

mkh
Starting Member

18 Posts

Posted - 2004-08-22 : 07:35:04

THIS DOES NOT WORK
I want to select all records which are 30 days old from the table with column off type date time

nr
SQLTeam MVY

12543 Posts

Posted - 2004-08-22 : 07:41:01

select *
from tbl
where dte >= dateadd(dd,-30,getdate())

or if just the day
select *
from tbl
where dte >= dateadd(dd,-30,convert(varchar(8),getdate(),112))

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mkh
Starting Member

18 Posts

Posted - 2004-08-22 : 09:49:01

Hi ,

Yes,the both queries seems ok for >= but for only oneday = does not work why??
both give null results for = (1 one day)

regards
MKh

nr
SQLTeam MVY

12543 Posts

Posted - 2004-08-22 : 14:19:20

>> find all entries from the last 30 days
Do you want that or just one days worth

select *
from tbl
where convert(varchar(8),dte,112) = dateadd(dd,-30,convert(varchar(8),getdate(),112))

select *
from tbl
where dte >= dateadd(dd,-30,convert(varchar(8),getdate(),112))
and dte < dateadd(dd,-29,convert(varchar(8),getdate(),112))

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mkh
Starting Member

18 Posts

Posted - 2004-08-23 : 04:49:03

Hi NK

Hi, Yes your first query works fine for user of 1 day
thanks,
mkh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources