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

Question

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 1

How about

SELECT *
FROM TABLE
WHERE getdate() BETWEEN startDate AND endDate

EDIT

You should have a lok at this link

and try something like Date/Time Conversions Using SQL Server

DECLARE @Table TABLE(
        startDate smalldatetime,
        endDate smalldatetime,
        value1 int
)

INSERT INTO @Table SELECT '02 Feb 2010 16:01', '02 Feb 2010 18:00', 1
INSERT INTO @Table SELECT '02 Feb 2010 21:00', '02 Feb 2010 22:00', 2
INSERT INTO @Table SELECT '02 Feb 2010 05:00', '02 Feb 2010 22:00', 3

DECLARE @MyGetDate DATETIME

SELECT @MyGetDate = '2 Mar 2003 21:40'

SELECT  * 
FROM @Table
WHERE convert(varchar, @MyGetDate, 14) BETWEEN convert(varchar, startDate, 14) AND convert(varchar, endDate, 14)

Answer 2

    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