find records between todays date and six months ago 1

[mrsbean]

I want to run a query which will use getdate and dateadd to look at records which match a date within a six month (180 day) range.

The following query returns 0 records. There are thousands of records that I should see, but I think there is something wrong with my formatting, so it doesn't find any matches:

SELECT     dbo.[Meter Activity].*, Date AS Expr1
FROM         dbo.[Meter Activity]
WHERE     (Date BETWEEN CONVERT(datetime, GETDATE(), 110) AND CONVERT(datetime, DATEADD(dd, - 180, GETDATE()), 110))

All help is much appreciated.

MrsBean

 

[bborissov]

SELECT dbo.[Meter Activity].*, Date AS Expr1
FROM   dbo.[Meter Activity]
WHERE CONVERT(varchar(8), Date, 112) BETWEEN 
      CONVERT(varchar(8), DateAdd(mm,-6,GETDATE()), 112)  AND
      CONVERT(varchar(8), GETDATE(), 112)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.