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

noone wrote:

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

>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:
> On Mon, 25 Sep 2006 17:49:33 +0100, noone wrote:
>
> >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