0

What is the best way to get records between dates taking into account only date part only? I mean getting only eg 2012-07-30

the following does not seem to work

DECLARE @StartDate datetime,@EndDate datetime,@NewStartDate dateTime
SET @StartDate='2012-06-03 17:43:56.220'
SET @EndDate='2012-07-30 00:00:00.000'

SELECT  *
FROM    MyTable
WHERE CONVERT(varchar(10),StartDate,111) >=CONVERT(varchar(10),@StartDate,111)
AND CONVERT(varchar(10),EndDate,111) <=CONVERT(varchar(10),@EndDate,111)
user9969
  • 15,632
  • 39
  • 107
  • 175
  • use [DATEADD/DATEDIFF](http://stackoverflow.com/questions/1177449/best-approach-to-remove-time-part-of-datetime-in-sql-server) to remove the time part of a date. Varchar conversion is inefficient. E.g. `DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))`. When you upgrade to SQL-Server 2008 or later you can use `CAST(GETDATE() AS DATE)` – GarethD May 23 '12 at 09:47
  • Thanks for the suggestions will do that – user9969 May 23 '12 at 10:02

1 Answers1

0
 select CONVERT(varchar(10),getdate(),111)

prints

2012/05/23

So it definitely strips the date part. What makes you think your query "does not seem to work" ?

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • Not sure why i was getting something different.mmmm You are right it does work.Is there a better way to do this? – user9969 May 23 '12 at 09:44
  • Your method works fine. You could also use the method from GartethD's comment. If you have version 2008, `cast(getdate() as date)` is definitely superior. – Andomar May 23 '12 at 09:49