On Mon, 25 Sep 2006 17:49:33 +0100, noone wrote:
Quote:
>Hi,
>
>I am designing an application which displays news topics until midnight
>on the DisplayUntil date and then they should drop out. Unfortunately,
>they seem to be dropping out at mid-day.
>
>I'm storing the 'DisplayFrom' and 'DisplayUntil' dates as SmallDateTime
>fields so the date is in the format "25/09/2006 00:0:00" and I'm
>comparing them with GetDate() which (I suspect) includes the actual time
>as well.
>
>I'm using the following SQL..
>
>SELECT *
>FROM t_news
>WHERE n_DisplayUntil >= GetDate() AND n_DisplayFrom <= GetDate()
>
>in MS SQL Server Express 2005
>
>I think this is probably all too simplistic - is there a way to compare
>these dates so that they display from the beginning of the start date
>(ie 00:00:01) until the end of the last day (ie 23:59:59)?
>
>I'm getting so desparate that I'm even considering pulling the whole
>recordset and doing the comparison in VBScript - and that's desparate!
>Can anyone help me, please?
>
>Regards
>ukwebguy
Hi ukwebguy,
First, read the information in Tibor's "ultimate guide to the datetime
datatype":
http://www.karaszi.com/SQLServer/info_datetime.asp.
If the DisplayFrom and DisplayUntil columns contain a date only (ie, the
time part is set to the default value of midnight), you could use this:
DECLARE @Today smalldatetime
-- Strip time portion from current date and time
SET @Today = DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
SELECT *
FROM news
WHERE DisplayUntil >= @Today
AND DisplayFrom <= @Today
If DisplayFrom and DisplayUntil contain time portions as well, then you
need to clarify your requirements. Here's a syntax that MIGHT work for
you, but only if my guesswork about your actual requirements is correct.
DECLARE @Today smalldatetime
-- Strip time portion from current date and time
SET @Today = DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
SELECT *
FROM news
WHERE DisplayUntil >= @Today
AND DisplayFrom < @Today + 1
--
Hugo Kornelis, SQL Server MVP
