eugeneK eugeneK - 6 months ago 15
SQL Question

TSQL, How to get smalldatetime's time between two smalldatetime's times?

I have Table with two smalldatetime columns, where one is startTime and other one is endTime.
I need to select all values from table which between times of both columns compared to getdate()' time.

I'm using SQL-Server 2005.

example


  • startTime endTime value1

  • 2/2/01 16:00 2/2/01 18:00 1

  • 2/2/01 21:00 2/2/01 22:00 2

  • 2/2/01 05:00 2/2/01 22:00 3



select getdate() gives 2/2/03 21:40 time is the only important factor

so i need to get 2 and 3

thanks in advance

Answer
    SELECT value1
      FROM tableA
     WHERE CONVERT(varchar, GETDATE(), 108) 
   BETWEEN CONVERT(varchar, Table_1.StartDate, 108) 
       AND CONVERT(varchar, Table_1.EndDate, 108);

Should do the trick