|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
DateDiff questionTechnically, 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 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)) -- Show quote--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 Show quote "Brian Lawton" <brian.k.law***@redtailcreek.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... >> 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 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. -- Show quote--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.law***@redtailcreek.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... >>> 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 >>> >> >> > > On Thu, 15 Sep 2005 15:53:39 -0700, Willie Bodger wrote:
>Technically, what is the difference between these two pieces: Hi Willie,> datediff(day,ONYX.dbo.Individual.dtInsertDate, getdate())>=365 > datediff(y,ONYX.dbo.Individual.dtInsertDate, getdate())>=365 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 Could you post an example where the results are different? I ust tested>different, so which one will actually give me a record that is >= one year >old? 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)  |
|||||||||||||||||||||||
Other interesting topics