DateDiff question

Technically, what is the difference between these two pieces:
datediff(day,ONYX.dbo.Individual.dtInsertDate, getdate())>=365
datediff(y,ONYX.dbo.Individual.dtInsertDate, getdate())>=365
I know one does day and one does day of year and their results are slightly
different, so which one will actually give me a record that is >= one year
old?
WillieIf you want all the rows more then 1 year old and you also want to correctly
handle leap years, why not use the following:
WHERE dtInsertDate <= dateadd(yy, -1, convert(varchar(10), dtInsertDate,
101))
--Brian
(Please reply to the newsgroups only.)
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:%23bviLhkuFHA.3400@.TK2MSFTNGP14.phx.gbl...
> Technically, what is the difference between these two pieces:
> datediff(day,ONYX.dbo.Individual.dtInsertDate, getdate())>=365
> datediff(y,ONYX.dbo.Individual.dtInsertDate, getdate())>=365
> I know one does day and one does day of year and their results are
> slightly different, so which one will actually give me a record that is >=
> one year old?
> Willie
>|||Sorry, I'm a little slow sometimes, but do you mean
WHERE dtInsertDate <= dateadd(yy, -1, convert(varchar(10), getdate(),
101))? Otherwise I don't see how it gets today's date to check from? And
then, would it work to just use getdate() instead of the whole convert
thing, or do I need that to properly handle the dateadd?
Thanks,
Willie
"Brian Lawton" <brian.k.lawton@.redtailcr.com> wrote in message
news:u%23BqQJluFHA.664@.tk2msftngp13.phx.gbl...
> If you want all the rows more then 1 year old and you also want to
> correctly handle leap years, why not use the following:
> WHERE dtInsertDate <= dateadd(yy, -1, convert(varchar(10), dtInsertDate,
> 101))
> --
> --Brian
> (Please reply to the newsgroups only.)
>
> "Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
> news:%23bviLhkuFHA.3400@.TK2MSFTNGP14.phx.gbl...
>|||Sorry about that. You're correct, the GETDATE () needs to be inside of the
convert. I included the CONVERT to ensure that the time portion of the
GETDATE() return value is truncated thereby forcing a consistent comparison
to midnight rather than arbitrary comparison based on the current run time.
--Brian
(Please reply to the newsgroups only.)
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:OprzcbuuFHA.3048@.TK2MSFTNGP10.phx.gbl...
> Sorry, I'm a little slow sometimes, but do you mean
> WHERE dtInsertDate <= dateadd(yy, -1, convert(varchar(10), getdate(),
> 101))? Otherwise I don't see how it gets today's date to check from? And
> then, would it work to just use getdate() instead of the whole convert
> thing, or do I need that to properly handle the dateadd?
> Thanks,
> Willie
> "Brian Lawton" <brian.k.lawton@.redtailcr.com> wrote in message
> news:u%23BqQJluFHA.664@.tk2msftngp13.phx.gbl...
>|||On Thu, 15 Sep 2005 15:53:39 -0700, Willie Bodger wrote:

>Technically, what is the difference between these two pieces:
> datediff(day,ONYX.dbo.Individual.dtInsertDate, getdate())>=365
> datediff(y,ONYX.dbo.Individual.dtInsertDate, getdate())>=365
Hi Willie,
As far as I know, there is no diffference at all. The y parameter only
differs from the day parameter in the context of the DATEPART function,
not in the context of DATEDIFF.

>I know one does day and one does day of year and their results are slightly
>different, so which one will actually give me a record that is >= one year
>old?
Could you post an example where the results are different? I ust tested
it on a cross self-join of a table with all dates from 2000 up to and
including 2005, and I didn't found a single combination of dates where
they differ.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)