|
|
Posted by Madhivanan on 11/28/13 11:59
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
Navigation:
[Reply to this message]
|