Date comparison problem

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
ukwebguynoone wrote:

Quote:

Originally Posted by

I'm getting so desparate that I'm even considering pulling the whole
recordset and doing the comparison in VBScript - and that's desparate!

If for some reason you can't get the back-end logic to work correctly,
at least try to get it to work almost-correctly (e.g. add/subtract one
day using the DateDiff function) and then use VBS to finish the job.|||On Mon, 25 Sep 2006 17:49:33 +0100, noone wrote:

Quote:

Originally Posted by

>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|||http://sql-server-performance.com/fk_datetime.asp
Madhivanan

Hugo Kornelis wrote:

Quote:

Originally Posted by

On Mon, 25 Sep 2006 17:49:33 +0100, noone wrote:
>

Quote:

Originally Posted by

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