0

I want to use BETWEEN clause in this query and don't know how to do this

SELECT * FROM record 
WHERE  (DATEPART(yy, register_date) = 2009
AND    DATEPART(mm, register_date) = 10
AND    DATEPART(dd, register_date) = 10)
Danish Bhatti
  • 57
  • 1
  • 9
  • e.g. `WHERE DATEPART(YEAR, register_date) BETWEEN X AND Y`? – ZLK Apr 20 '17 at 05:17
  • I have to use both year and month in between clause like register_date BETWEEN '2009-10' and '2009-12' and like clause doesn't work for me – Danish Bhatti Apr 20 '17 at 05:22
  • So like `WHERE DATEPART(YEAR, register_date) = 2009 AND DATEPART(MONTH, register_date) BETWEEN 9 AND 12`? Alternatively, you can skip the datepart altogether and just say `WHERE register_date >= '2009-10-10' AND register_date < '2010-12-11'` or something like that. – ZLK Apr 20 '17 at 05:27
  • i can not use this because i have to use like clause – Danish Bhatti Apr 20 '17 at 05:32
  • use between of what? two dates? what? your example seems like you don't need between clause – Vijunav Vastivch Apr 20 '17 at 05:51
  • Possible duplicate of [SQL Server datetime LIKE select?](http://stackoverflow.com/questions/1629050/sql-server-datetime-like-select) – Zulatin Apr 20 '17 at 06:00
  • i want record of time period between '2009-10' and '2010-12' – Danish Bhatti Apr 20 '17 at 06:07
  • So you want everything that occurred between October 2009 and December 2010, inclusive? That doesn't seem to need to use `DATEPART` at all - just compute your start and end dates (Usually better to specify an exclusive end date if your records include times as well as dates) and use a simple comparison. If that's not right, please put more effort into your question. [edit] it and include *sample data* and *expected results*. (i.e. have some sample data that *shouldn't* be in the result and make it clear *why* rows are/are not to be included) – Damien_The_Unbeliever Apr 20 '17 at 06:49

1 Answers1

0

This gives the records from 5 hours ago until now

SELECT * FROM record
WHERE register_date BETWEEN DATEADD(HOUR, -5, GETDATE()) AND GETDATE()

It appears this might be what you want

select * from record
where register_date between '2017-10-1' and '2017-12-31'

Do note that if you want all records from the last day you might want to add time or pick the day after as the default time is 0:00

Zulatin
  • 273
  • 1
  • 3
  • 14