How to correctly use the getdate() function with t

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

SQL Server 2005 Forums

Transact-SQL (2005)

How to correctly use the getdate() function with t

Author

Topic

pras2007
Posting Yak Master

216 Posts

Posted - 2009-09-22 : 08:25:11

Good Morning All,

I have a date/time field that stored the value as 2009-09-22 08:18:54.910, the problem is that I want to query that field and having difficulties. A sample of the code is below:
select *
from TESTING
where update_date = convert(char(10), getdate()-1, 110)

The query above is not returning any records, what am I doing wrong? Please help.

Thanks.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-09-22 : 08:29:10

select *
from TESTING
where update_date >= convert(char(10), getdate()-1, 110) and update_date <= convert(char(10), getdate(), 110)

Or:

select *
from TESTING
where DATEADD(dd, DATEDIFF(dd, 0, update_date), 0) = DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-22 : 09:37:07

If you want to get yesterday's data,

select *
from TESTING
where
update_date>=dateadd(day,datediff(day,0,getdate()),-1)
and
update_date<dateadd(day,datediff(day,0,getdate()),0)

Madhivanan

Failing to plan is Planning to fail

pras2007
Posting Yak Master

216 Posts

Posted - 2009-09-22 : 16:34:40

Thanks for the response folks! If I want to retreive the pervoius month's date, how would I accomplish that without hardcoding the date ranges?

Please advise.

Udayantha
Starting Member

4 Posts

Posted - 2009-09-23 : 01:16:57

You can get the last month date from this

select dateadd(mm,-1,getdate()) as lastmonthdate

this returns a date. Is this what you require?

Bst Rgds,
Udayantha

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-23 : 02:25:37

select *
from TESTING
where
update_date>=dateadd(month,datediff(month,0,getdate())-1,0)
and
update_date<dateadd(month,datediff(month,0,getdate()),0)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources