> I read somewhere years ago that it's best to store dates with no
> times (00:00:00 actually) and then add an hour column.

"Rick" <no@spam.com> wrote in message
news:eI7LLM4hEHA.3928@TK2MSFTNGP11.phx.gbl...
> Hello,
>
> Once again my sql ambitions exceed my training and I turn to you for
> assistance. I read somewhere years ago that it's best to store dates with
no
> times (00:00:00 actually) and then add an hour column. This works great
for
> our needs because we have a counter stores it's results in the table this
> way.
>
> My SELECT statement uses BETWEEN on the date columns and it works as
> expected, however I can't get the SELECT to start from X day at N hour to
Y
> day at N hour and everything in between. What gets returned is X and Y
> dates with the hours of N.
>
> See code below, and thank you in advance for your help.
>
> Rick
>
>
> DECLARE @table table (
> myDate datetime
> , myHour int
> );
> DECLARE @startHour INT;
> DECLARE @endHour INT;
> SELECT @startHour = 12;
> SELECT @endHour = 20;
>
> INSERT INTO @table (myDate, myHour) VALUES ('8/21/2004', 1);
> INSERT INTO @table (myDate, myHour) VALUES ('8/21/2004', 12);
> INSERT INTO @table (myDate, myHour) VALUES ('8/21/2004', 20);
> INSERT INTO @table (myDate, myHour) VALUES ('8/23/2004', 2);
> INSERT INTO @table (myDate, myHour) VALUES ('8/23/2004', 15);
> INSERT INTO @table (myDate, myHour) VALUES ('8/23/2004', 23);
>
>
> SELECT *
> FROM @table
> WHERE (myDate BETWEEN '8/21/2004' AND '8/25/2004')
> --AND myHour >= @startHour OR myHour <= @endHour /* no good */
> --AND myHour >= @startHour AND myHour <= @endHour /* no good */
> --AND (myHour BETWEEN @startHour AND @endHour) /* no good */
> ORDER BY myDate, myHour
>
>

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message
news:vI6dnRepFcIOy7rcRVn-sw@giganews.com...
> > I read somewhere years ago that it's best to store dates with no
> > times (00:00:00 actually) and then add an hour column.
>
> Maybe "years ago" in some other database that made sense; not likely in
SQL
> Server 2000. Drop the hour column and use DATETIME the way it was
intended.
>
> Always specify dates in one of the ISO formats so that they will be
> interpreted correctly regardless of regional date settings. Any of the
> following are supported:
>
> '20031231' -- Just the date
> '2003-12-31T17:59:00' -- Date/hours/minutes/seconds
> '2003-12-31T17:59:00.000' -- Date/hours/minutes/seconds/milliseconds
>
> Also notice that it's normally easier to use >= and < with dates and times
> rather than BETWEEN. For example:
>
> SELECT *
> FROM YourTable
> WHERE dt >= '2004-08-21T12:00:00'
> AND dt < '2004-08-25T21:00:00'
>
> is equivalent to:
>
> SELECT *
> FROM YourTable
> WHERE dt
> BETWEEN '2004-08-21T12:00:00'
> AND '2004-08-25T20:59:59.997'
>
> --
> David Portas
> SQL Server MVP
> --
>
>