0

I know that this is very popular question and I found an answer to it, but not optimal.

So the task is - I need to know which of the employees are on vacation at the moment if start day of the vacation is bigger or equal than today and end date of the vacation is bigger or equal than today.

The problem is that dates of start and end of vacation are datetime so I can't compare it with getDate(). Varchar conversion of dates doesn't works also.

I solved the task by this way:

WHERE
(
YEAR (vac.e_dateTo) = YEAR (getDate()) AND
MONTH (vac.e_dateTo) = MONTH (getDate()) AND
DAY (vac.e_dateTo) >= DAY (getDate()) AND
DAY (vac.e_dateFrom) <= DAY (getDate())
)

but it seems to me that it can be done with cast/convert but I don't underestand how. Will be greatfull for any advise.

Cove
  • 655
  • 3
  • 7
  • 17
  • 1
    `getdate()` returns `datetime`. What's the issue with using it? – shree.pat18 Nov 13 '15 at 08:27
  • So, all vacations have to start and end within the same month? This question would be immensely improved if you'd add some *sample data* and expected results. – Damien_The_Unbeliever Nov 13 '15 at 08:29
  • The issue with getdate() and vac.e_dateFrom is time: getDate() = 2015-11-13 11:22:49.017 and vac_edateFrom = 2015-11-13 00:00:00.000 so I can't compare. – Cove Nov 13 '15 at 08:31
  • 1
    And the links under "Related" on the right, such as [Compare dates in T-SQL ignoring the time part](http://stackoverflow.com/questions/1427469/compare-dates-in-t-sql-ignoring-the-time-part?rq=1) don't contain a suitable answer? – Damien_The_Unbeliever Nov 13 '15 at 08:32
  • Damien_The_Unbeliever, cast(floor(cast(getdate() as float)) as datetime) suits me, thanks a lot. If you write it as answer I'll accept it. – Cove Nov 13 '15 at 08:34

0 Answers0