date comparison

11 Aug 2006 10:15 PM

Samuel Shulman

Hi

In order to retrieve records with a specified date but without a specified
time I find that the '=' character will return only records with the same
time as well, so if I compare '2006-08-09' to '2006-08-09 10:12:55' that
record will not return

Is there a way to solve that?
Thank you,
Samuel

11 Aug 2006 3:34 PM

Tibor Karaszi

See http://www.karaszi.com/SQLServer/info_datetime.asp and more specifically
http://www.karaszi.com/SQLServer/info_datetime.asp#Searching.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

Show quote

"Samuel Shulman" <samuel.shul***@ntlworld.com> wrote in message
news:ui2xvrVvGHA.4612@TK2MSFTNGP02.phx.gbl...
> Hi
>
> In order to retrieve records with a specified date but without a specified time I find that the
> '=' character will return only records with the same time as well, so if I compare '2006-08-09' to
> '2006-08-09 10:12:55' that record will not return
>
> Is there a way to solve that?
> Thank you,
> Samuel
>

11 Aug 2006 7:57 PM

Aaron Bertrand [SQL Server MVP]

WHERE col >= '20060809'
AND col < '20060810'

Show quote

"Samuel Shulman" <samuel.shul***@ntlworld.com> wrote in message
news:ui2xvrVvGHA.4612@TK2MSFTNGP02.phx.gbl...
> Hi
>
> In order to retrieve records with a specified date but without a specified
> time I find that the '=' character will return only records with the same
> time as well, so if I compare '2006-08-09' to '2006-08-09 10:12:55' that
> record will not return
>
> Is there a way to solve that?
> Thank you,
> Samuel
>

12 Aug 2006 1:14 PM

Henry

> WHERE col >= '20060809'
> AND col < '20060810'
>

Beware that you will only have records from 20060810 00:00:00.000 and
nothing else from that day.

cheers

12 Aug 2006 1:19 PM

Henry

23D$4IFhvGHA.1***@TK2MSFTNGP02.phx.gbl...

>> WHERE col >= '20060809'
>> AND col < '20060810'
>>
> Beware that you will only have records from 20060810 00:00:00.000 and
> nothing else from that day.
>

Sorry, I've overlooked that there wasn't any equal in the last statement!
You won't get anything from 20060810 at all, which could be exactlly what
you want.

cheers

12 Aug 2006 12:12 AM

Cowboy (Gregory A. Beamer)

Assuming you are sending in the 00:00:00.000 as the
hour:minute:second.millisecond, you can then add one day and run the query.

WHERE date >= @var1
AND date < @var2

OR date between @var1 and @var2 (although you have to subtract one
millisecond from the start).

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think outside of the box!
*************************************************

Show quote

"Samuel Shulman" <samuel.shul***@ntlworld.com> wrote in message
news:ui2xvrVvGHA.4612@TK2MSFTNGP02.phx.gbl...
> Hi
>
> In order to retrieve records with a specified date but without a specified
> time I find that the '=' character will return only records with the same
> time as well, so if I compare '2006-08-09' to '2006-08-09 10:12:55' that
> record will not return
>
> Is there a way to solve that?
> Thank you,
> Samuel
>

12 Aug 2006 1:11 PM

Henry

> Is there a way to solve that?
> Thank you,
> Samuel

Please set you PC clock correct before posting, no reason that your posting
should stay on top for about a week ;o)

cheers