How to query database where date and time field are separate?

Question

In Table, Time and date fields are two separate fields. I need to make a query something like this give all records where date and time <= givenDateTime. How do i do that please?

EDIT My query:-

select DateField, TimeField from Table1 where DateField <= '07/26/2009' and TimeField <= '16:26:20'

Result is:-

2008-04-22 00:00:00.000 1899-12-30 23:59:58.000

2009-04-15 00:00:00.000 1899-12-30 23:59:56.000

2006-06-06 00:00:00.000 1899-12-30 23:59:53.000

See the Time above in results. Its wrong

Answer 1

Break up the @givenDateTime to the date component and the time component and use AND to build up the WHERE clause:

DECLARE @givenDate DATETIME 
SELECT @givenDate = DATEADD(dd, 0, DATEDIFF(dd, 0, @givenDateTime))

DECLARE @givenTime DATETIME 
SELECT @givenTime =  CONVERT(varchar(2),
      CASE
           WHEN DATEPART([hour], @givenDateTime) > 12 THEN CONVERT(varchar(2), (DATEPART([hour], @givenDateTime) - 12))
           WHEN DATEPART([hour], @givenDateTime) = 0 THEN '12'
           ELSE CONVERT(varchar(2), DATEPART([hour], @givenDateTime))
      END
 ) + ':' +
 CONVERT(char(2), SUBSTRING(CONVERT(char(5), @givenDateTime, 108), 4, 2)) + ' ' +
 CONVERT(varchar(2),
      CASE
           WHEN DATEPART([hour], @givenDateTime) > 12 THEN 'PM'
           ELSE 'AM'
      END
 ) 

SELECT * FROM myTable
WHERE tableDate <= @givenDate AND tableTime <= @givenTime

Date conversion taken from here. Time conversion taken from here.

Note that the @givenTime will have a date of 01 Jan 1900 (plus the time), so be sure that your where clause takes that into account.

Answer 2

You can add datetime values naively.

http://cloudexchange.cloudapp.net/stackoverflow/q/2558

Note that this may not be SARG'able.

I'm assuming here that one column is SQL Server 2008 DATE and one is TIME.

-- Datetime Combination Example 
SELECT CAST(dt AS datetime) + CAST(tm AS datetime) AS dtm
FROM (
    SELECT CAST('6/7/2010' AS DATE) AS dt, CAST('12:34:00' AS TIME) AS tm
) AS X

If they are already both datetime:

SELECT dt + tm AS dtm
FROM (
    SELECT CAST('6/7/2010' AS DATETIME) AS dt, CAST('12:34:00' AS DATETIME) AS tm
) AS X

Answer 3

With your data model:

select  DateField, 
        TimeField 
from    Table1 
where   (DateField < '07/26/2009')
    or  (DateField = '07/26/2009' and TimeField <= '16:26:20')

But ideally you can have a computed column that would have complete date and time DateTimeField, and just query relative to this one. If this is the query you do often, then you could make this computed column persisted and even create indexes on this column. Creating this computed column in SQL Server 2008 is trivial if you use DATE and TIME data types (as shown in Cade's answer):

DateTimeField AS CONVERT(DATETIME, DateField + TimeField) PERSISTED NOT NULL

In case of SQL Server 2005 you might need to do some conversion.