get the last 14 days

monfu

Member
Joined
Jul 2, 2004
Messages
8
Location
Malta
Programming Experience
3-5
[FONT=Verdana, Arial, Helvetica][FONT=Verdana, Arial, Helvetica] Dear All,

I am creating a report to get the last transaction done between today and the last 14 days

My where clause is like this:-

WHERE (date_posted >= GETDATE()) AND (date_posted <= GETDATE() - 14)

for some reason its not working fine

Is the syntax correct?
[/FONT][/FONT]
 

TechGnome

Well-known member
Joined
May 23, 2005
Messages
896
Programming Experience
10+
Don't use DateDiff.....
DateAdd should work better....

VB.NET:
SELECT * FROM myTable
WHERE date_posted BETWEEN DateAdd(d, -14, GETDATE()) AND GETDATE()

-tg
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,553
Location
Sydney, Australia
Programming Experience
10+
Tarik said:
why Dateadd is better than using Datediff ?
I'm going to give my own opinion here and say that DATEADD returns a Date, which makes the statement more consistent and allows you to use the BETWEEN operator, which is the way it should be done if you are trying to find something between two limits.
 

TechGnome

Well-known member
Joined
May 23, 2005
Messages
896
Programming Experience
10+
Consider this:
Date1 = '1/1/2006'
Date2 = '1/14/2006'
DateDiff(Date1, Date2) = 13... not 14....
Also if you look for where datediff(day,date_posted,GETDATE())= 14 then you will miss where the datediff =13, 12,11, etc.

-tg
 

TechGnome

Well-known member
Joined
May 23, 2005
Messages
896
Programming Experience
10+
I think you missed my point... even if date_posted is passed in (which it shouldn't since it is the name of the field).... datediff(day,date_posted,GETDATE())= 14 will only return things EXACTLY 14 days old - and not the stuff in between.

DateAdd is safer because it is all dates - apples to apples.

-tg
 
Top Bottom