-3

I have a table for time off requests with a start date and end date. I want to display the records between start date and end date that match only today. Is there a way to display today's records only between a date range?

I then need to do the same thing in a separate sql query for tomorrow.

user2498035
  • 1
  • 1
  • 2
  • 3
    This question does not show any research effort. It is important to **do your homework**. Tell us what you found and ***why*** it didn't meet your needs. This demonstrates that you've taken the time to try to help yourself, it saves us from reiterating obvious answers, and most of all it helps you get a more specific and relevant answer. [FAQ](http://stackoverflow.com/questions/how-to-ask). – Kermit Jun 18 '13 at 17:08
  • Check out http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap/325964#325964 – Charles Bretana Jun 18 '13 at 17:13
  • I have already tried: CAST(CONVERT(VARCHAR,GETDATE(),101) AS DATETIME) BETWEEN STARTINGON AND ENDINGON That returns too much. I've tried: startingon <= CAST(CONVERT(VARCHAR,GETDATE(),101) AS DATETIME) AND CAST(CONVERT(VARCHAR,GETDATE(),101) AS DATETIME) < endingon Still too much – user2498035 Jun 18 '13 at 17:33
  • @CharlesBretana That is for two date ranges overlapping – user2498035 Jun 18 '13 at 17:38

1 Answers1

0
SELECT *
FROM MyTable
WHERE Date() >= StartDate 
  AND Date() =< EndDate
  AND DateField = Date()

This will give you all the records with a date of today's date that are between the start and end date fields. Obviously, it will only work for the date you run the query.

You MAY be able to use the BETWEEN operator to clean that up (i.e. Date() BETWEEN StartDate and EndDate) but I'm not positive about that.

Tony
  • 9,672
  • 3
  • 47
  • 75
Johnny Bones
  • 8,786
  • 7
  • 52
  • 117
  • Right this won't work because there is a start date and an end date so I need to display everything that is equal to today's date only if it's between start date and end date. – user2498035 Jun 18 '13 at 17:29
  • See my edit for an updated code that should work. – Johnny Bones Jun 18 '13 at 17:38
  • So where is the datefield value coming from? – user2498035 Jun 18 '13 at 17:46
  • Maybe I'm not understanding "display today's records only between a date range". How are you determining it's today's date? Does either StartDate or EndDate have to be today's date? What if StartDate is 5 days ago and EndDate is tomorrow? Can you possibly add some sample data like this guy did: http://stackoverflow.com/questions/17171267/sorting-a-grouped-report/17172020#17172020 – Johnny Bones Jun 18 '13 at 18:04
  • I resolved this using the following: Select TimeOffRequestID, yourname AS "Employee Name", Replace(forwardcallsto, 'Select One', 'Voicemail') AS "Forward Calls To", appointment AS "Comments" FROM dbo.nei_TimeOffRequest WHERE CAST(CONVERT(VARCHAR,GETDATE(),101) AS DATETIME) BETWEEN startingon AND endingon ORDER BY yourname ASC – user2498035 Jun 18 '13 at 19:56
  • Your answer was closest to what I wanted SELECT * FROM MYTABLE WHERE DATE() BETWEEN STARTDATE AND ENDDATE – user2498035 Jun 18 '13 at 19:59
  • Very kind of you, thanks! :o) – Johnny Bones Jun 18 '13 at 20:01