|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Selecting the Nearest DateI want to select the nearest date from a database table, based on the
current date. What is the most efficient way of doing this? http://www.devx.com/vb2themax/Article/19902 I'm reading that article and it talks about casting to float, is this honestly the best way to do it? Regards, Davie. Try adding
HAVING myDate = MIN(Datediff(dd, myDate, getdate())) Show quote "Davie" wrote: > I want to select the nearest date from a database table, based on the > current date. What is the most efficient way of doing this? > > http://www.devx.com/vb2themax/Article/19902 > > I'm reading that article and it talks about casting to float, is this > honestly the best way to do it? > > Regards, > Davie. > > > Hi
This gives you even milliseconds difference MIN(Datediff(ms, myDate, getdate())) If you have performance problem use case and decide whether day difference of min difference Regards R.D Show quote "RioDD" wrote: > Try adding > HAVING myDate = MIN(Datediff(dd, myDate, getdate())) > > "Davie" wrote: > > > I want to select the nearest date from a database table, based on the > > current date. What is the most efficient way of doing this? > > > > http://www.devx.com/vb2themax/Article/19902 > > > > I'm reading that article and it talks about casting to float, is this > > honestly the best way to do it? > > > > Regards, > > Davie. > > > > > > Davie,
If you just want to select the nearest date of current date, which is less than current date you can do the following. SELECT Max(datecol) FROM YourTable WHERE datecol < CURRENT_TIMESTAMP Show quote "Davie" <Davie@newsgroup.nospam> wrote in message news:evp8tpStFHA.2472@TK2MSFTNGP14.phx.gbl... >I want to select the nearest date from a database table, based on the >current date. What is the most efficient way of doing this? > > http://www.devx.com/vb2themax/Article/19902 > > I'm reading that article and it talks about casting to float, is this > honestly the best way to do it? > > Regards, > Davie. > or
for nearest past date SELECT TOP 1 FROM MYTABLE WHERE MYDATECOL <= GETDATE() ORDER BY MYDATECOL DESC for nearest future date SELECT min(datecol) FROM MYTABLE WHERE MYDATECOL >= GETDATE() ORDER BY MYDATECOL DESC Regards R.D Show quote "Roji. P. Thomas" wrote: > Davie, > > If you just want to select the nearest date of current date, which is less > than current date > you can do the following. > > SELECT Max(datecol) > FROM YourTable > WHERE datecol < CURRENT_TIMESTAMP > -- > Roji. P. Thomas > Net Asset Management > http://toponewithties.blogspot.com > > > "Davie" <Davie@newsgroup.nospam> wrote in message > news:evp8tpStFHA.2472@TK2MSFTNGP14.phx.gbl... > >I want to select the nearest date from a database table, based on the > >current date. What is the most efficient way of doing this? > > > > http://www.devx.com/vb2themax/Article/19902 > > > > I'm reading that article and it talks about casting to float, is this > > honestly the best way to do it? > > > > Regards, > > Davie. > > > > > I Mean
1)SELECT TOP 1 mydatecol 2)SELECT min(mydatecol) Show quote "R.D" wrote: > or > for nearest past date > SELECT TOP 1 FROM MYTABLE WHERE MYDATECOL <= GETDATE() ORDER BY MYDATECOL > DESC > for nearest future date > SELECT min(datecol) FROM MYTABLE WHERE MYDATECOL >= GETDATE() ORDER BY > MYDATECOL DESC > > Regards > R.D > > > "Roji. P. Thomas" wrote: > > > Davie, > > > > If you just want to select the nearest date of current date, which is less > > than current date > > you can do the following. > > > > SELECT Max(datecol) > > FROM YourTable > > WHERE datecol < CURRENT_TIMESTAMP > > -- > > Roji. P. Thomas > > Net Asset Management > > http://toponewithties.blogspot.com > > > > > > "Davie" <Davie@newsgroup.nospam> wrote in message > > news:evp8tpStFHA.2472@TK2MSFTNGP14.phx.gbl... > > >I want to select the nearest date from a database table, based on the > > >current date. What is the most efficient way of doing this? > > > > > > http://www.devx.com/vb2themax/Article/19902 > > > > > > I'm reading that article and it talks about casting to float, is this > > > honestly the best way to do it? > > > > > > Regards, > > > Davie. > > > > > > > > > hi davie try this
use northwind select top 10 orderid,shippeddate, abs(datediff(ss,getdate(),shippeddate)) from orders where shippeddate is not null --nwind has null shipped date order by 2 asc use top 1 instead -- Show quotethanks, ------------------------------------ Jose de Jesus Jr. Mcp,Mcdba Data Architect Sykes Asia (Manila philippines) MCP #2324787 "Davie" wrote: > I want to select the nearest date from a database table, based on the > current date. What is the most efficient way of doing this? > > http://www.devx.com/vb2themax/Article/19902 > > I'm reading that article and it talks about casting to float, is this > honestly the best way to do it? > > Regards, > Davie. > > > Many thanks guys.
It's appreciated. Davie. Show quote "Jose G. de Jesus Jr MCP, MCDBA" <Email me> wrote in message news:911EBA14-A54B-4544-93C7-884D44FD3677@microsoft.com... > hi davie try this > > use northwind > select top 10 orderid,shippeddate, abs(datediff(ss,getdate(),shippeddate)) > from orders > where shippeddate is not null --nwind has null shipped date > order by 2 asc > > > use top 1 instead > -- > thanks, > > ------------------------------------ > Jose de Jesus Jr. Mcp,Mcdba > Data Architect > Sykes Asia (Manila philippines) > MCP #2324787 > > > "Davie" wrote: > >> I want to select the nearest date from a database table, based on the >> current date. What is the most efficient way of doing this? >> >> http://www.devx.com/vb2themax/Article/19902 >> >> I'm reading that article and it talks about casting to float, is this >> honestly the best way to do it? >> >> Regards, >> Davie. >> >> >>  |
|||||||||||||||||||||||
Other interesting topics