SQLTeam.com | Weblogs | Forums

Orders outstanding before a certain date and time

I'm trying to find a way to combine date and time in a where clause so i can identify anything outstanding before a certain time , i have tried the below but could do with some pointers

DECLARE @day date
DECLARE @Time date
SET @day = GETDATE()
SET @day = DATEADD(DAY, DATEDIFF(day, 0, @day)-1, 0)
SET @Time = (Select convert(varchar(10), GETDATE(), 108))
SET @Time = '14:00:00'


select * 
From shipments 
where shipments.systemtype = 'N'
and shipments.CreatedDate <= @day and  shipments.CreatedDate <= @Time

order by CreatedDate desc

hi

what data type is "shipments.CreatedDate"
if it is datetime

then the way to do it is !!
DECLARE @date DATETIME, @time time
SET @date='2010-10-01'
SET @time='15:00:00'
SET @date=@date+CAST(@time AS DATETIME)
SELECT @date AS DATETIME

HI , thansk thats sorted it

DECLARE @day date
DECLARE @date DATETIME, @time time
SET @day = GETDATE()
SET @day = DATEADD(DAY, DATEDIFF(day, 0, @day)-1, 0)
SET @date= @day
SET @time='14:00:00'
SET @date=@date+CAST(@time AS DATETIME)


select * 
From shipments 
where shipments.systemtype = 'N'
and shipments.CreatedDate <= @date 

order by CreatedDate desc