current Date query

7 Sep 2006 2:00 PM

mark

i need to query a date between two values,

kindve like this :-

mydatecolumn between '07/09/2006 00:00:00' and '07/09/2006 12:00:00'

however i need those dates to be variable per day (SP) so :-

mydatecolumn between getdate() 00:00:00 and getdate() + 12:00:00

is this possible ?

ive been trying to use datepart to no success

cheers

mark

7 Sep 2006 2:08 PM

Aaron Bertrand [SQL Server MVP]

DECLARE
    @StartDate SMALLDATETIME,
    @EndDate SMALLDATETIME;

SELECT
    @StartDate = DATEDIFF(DAY, 0, GETDATE()),
    @EndDate = DATEADD(HOUR, 12, @StartDate);

SELECT
....
WHERE MyDateColumn >= @StartDate
    AND MyDateColumn < @EndDate;

I strongly recommend staying away from BETWEEN for date queries. If you
really mean to include noon on the nose, than change < to <=.

http://databases.aspfaq.com/general/should-i-use-between-in-my-database-queries.html

Show quoteHide quote

"mark" <m***@remove.com> wrote in message
news:YM-dnU25ndt6uJ3YRVnyjg@giganews.com...
>i need to query a date between two values,
>
> kindve like this :-
>
> mydatecolumn between '07/09/2006 00:00:00' and '07/09/2006 12:00:00'
>
> however i need those dates to be variable per day (SP) so :-
>
> mydatecolumn between getdate() 00:00:00 and getdate() + 12:00:00
>
>
> is this possible ?
>
> ive been trying to use datepart to no success
>
> cheers
>
> mark
>
>
>
>

7 Sep 2006 3:44 PM

mark

Show quote Hide quote

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:uxSdmbo0GHA.2356@TK2MSFTNGP03.phx.gbl...
> DECLARE
>    @StartDate SMALLDATETIME,
>    @EndDate SMALLDATETIME;
>
> SELECT
>    @StartDate = DATEDIFF(DAY, 0, GETDATE()),
>    @EndDate = DATEADD(HOUR, 12, @StartDate);
>
> SELECT
> ...
> WHERE MyDateColumn >= @StartDate
>    AND MyDateColumn < @EndDate;
>
>
> I strongly recommend staying away from BETWEEN for date queries. If you
> really mean to include noon on the nose, than change < to <=.
>
> http://databases.aspfaq.com/general/should-i-use-between-in-my-database-queries.html
>
thank you,

it was smalldatetime that threw me, im having one of "those" days....
modded it like this :-

DECLARE
    @CurrentDate SMALLDATETIME,
@Startdate smalldatetime,
    @EndDate SMALLDATETIME;

SELECT

    @CurrentDate = DATEDIFF(DAY, 0, GETDATE()),
    @StartDate = DATEADD(HOUR, 17, @CurrentDate),
    @EndDate = DATEADD(HOUR, 23, @CurrentDate);

for other times of day :)

thanks again

mark