Last two dates in T-SQL?

I have table which have a day-to-day information. How to use sql to
retrive the last two days information? Thanks.SELECT *
FROM MyTable
WHERE DATEDIFF(day, MyDateColumn, GETDATE()) <= 2
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
<gouqizi.lvcha@.gmail.com> wrote in message
news:1189544898.368595.123490@.e34g2000pro.googlegroups.com...
>I have table which have a day-to-day information. How to use sql to
> retrive the last two days information? Thanks.
>|||Ouch.
That will result in a table scan. Never put the column inside the function.
Always evaluate the column against a constant.
Try this
SELECT *
FROM MyTable
WHERE MyDateColumn >= DATEadd( GETDATE(), -2)
That will be for the last 48 hours. If you want to have since the second
midnight previous, that is a different query.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Daniel Jameson" <danjam47@.newsgroup.nospam> wrote in message
news:Oc5hPzL9HHA.4180@.TK2MSFTNGP05.phx.gbl...
> SELECT *
> FROM MyTable
> WHERE DATEDIFF(day, MyDateColumn, GETDATE()) <= 2
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>
> <gouqizi.lvcha@.gmail.com> wrote in message
> news:1189544898.368595.123490@.e34g2000pro.googlegroups.com...
>>I have table which have a day-to-day information. How to use sql to
>> retrive the last two days information? Thanks.
>|||Daniel
As Geoff indicates, you should not put the column inside a function.
However, the statement that the column must be evaluated against a constant
is a little too strict. The column should be all by itself, but can be
compared to an expression, including one containing the dateadd function, as
Geoff uses in his example.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:OuVHc9L9HHA.4200@.TK2MSFTNGP04.phx.gbl...
> Ouch.
> That will result in a table scan. Never put the column inside the
> function. Always evaluate the column against a constant.
>
> Try this
> SELECT *
> FROM MyTable
> WHERE MyDateColumn >= DATEadd( GETDATE(), -2)
> That will be for the last 48 hours. If you want to have since the second
> midnight previous, that is a different query.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Daniel Jameson" <danjam47@.newsgroup.nospam> wrote in message
> news:Oc5hPzL9HHA.4180@.TK2MSFTNGP05.phx.gbl...
>> SELECT *
>> FROM MyTable
>> WHERE DATEDIFF(day, MyDateColumn, GETDATE()) <= 2
>> --
>> Thank you,
>> Daniel Jameson
>> SQL Server DBA
>> Children's Oncology Group
>> www.childrensoncologygroup.org
>>
>> <gouqizi.lvcha@.gmail.com> wrote in message
>> news:1189544898.368595.123490@.e34g2000pro.googlegroups.com...
>>I have table which have a day-to-day information. How to use sql to
>> retrive the last two days information? Thanks.
>>
>|||Geoff, Kalen,
Thanks for the tip on functions doing table scans with column arguments. Is
that something I overlooked in Books Online, or is that one of those things
one learns only in situations like this? Though as I think about it now, it
would have to apply the function to every value in the column to evaluate
the expression. Furthermore, Geoff's WHERE clause will improve performance
only if there is an index on MyDateColumn; without the index a scan would
still be requried.
For the previous two calendar days:
declare @.midnight datetime;
set @.midnight = convert(datetime, floor(convert(float, getdate())))
SELECT *
FROM MyTable
WHERE MyDateColumn BETWEEN DATEADD(day, -2, @.midnight) AND
DATEADD(millisecond, @.midnight, -3)
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eCV56CM9HHA.4752@.TK2MSFTNGP04.phx.gbl...
> Daniel
> As Geoff indicates, you should not put the column inside a function.
> However, the statement that the column must be evaluated against a
> constant is a little too strict. The column should be all by itself, but
> can be compared to an expression, including one containing the dateadd
> function, as Geoff uses in his example.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:OuVHc9L9HHA.4200@.TK2MSFTNGP04.phx.gbl...
>> Ouch.
>> That will result in a table scan. Never put the column inside the
>> function. Always evaluate the column against a constant.
>>
>> Try this
>> SELECT *
>> FROM MyTable
>> WHERE MyDateColumn >= DATEadd( GETDATE(), -2)
>> That will be for the last 48 hours. If you want to have since the second
>> midnight previous, that is a different query.
>> --
>> Geoff N. Hiten
>> Senior SQL Infrastructure Consultant
>> Microsoft SQL Server MVP
>>
>>
>> "Daniel Jameson" <danjam47@.newsgroup.nospam> wrote in message
>> news:Oc5hPzL9HHA.4180@.TK2MSFTNGP05.phx.gbl...
>> SELECT *
>> FROM MyTable
>> WHERE DATEDIFF(day, MyDateColumn, GETDATE()) <= 2
>> --
>> Thank you,
>> Daniel Jameson
>> SQL Server DBA
>> Children's Oncology Group
>> www.childrensoncologygroup.org
>>
>> <gouqizi.lvcha@.gmail.com> wrote in message
>> news:1189544898.368595.123490@.e34g2000pro.googlegroups.com...
>>I have table which have a day-to-day information. How to use sql to
>> retrive the last two days information? Thanks.
>>
>>
>|||Daniel
It may be buried in the Books Online somewhere, but any book or article on
query tuning will mention that the optimizer will never decide to do an
index seek unless the column with an index is all by itself in a WHERE
clause.
And of course, you're completely right, that if there is no index on
MyDateColumn, it's a moot point.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Daniel Jameson" <danjam47@.newsgroup.nospam> wrote in message
news:uWx6KMM9HHA.1184@.TK2MSFTNGP04.phx.gbl...
> Geoff, Kalen,
> Thanks for the tip on functions doing table scans with column arguments.
> Is that something I overlooked in Books Online, or is that one of those
> things one learns only in situations like this? Though as I think about
> it now, it would have to apply the function to every value in the column
> to evaluate the expression. Furthermore, Geoff's WHERE clause will
> improve performance only if there is an index on MyDateColumn; without the
> index a scan would still be requried.
> For the previous two calendar days:
> declare @.midnight datetime;
> set @.midnight = convert(datetime, floor(convert(float, getdate())))
> SELECT *
> FROM MyTable
> WHERE MyDateColumn BETWEEN DATEADD(day, -2, @.midnight) AND
> DATEADD(millisecond, @.midnight, -3)
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eCV56CM9HHA.4752@.TK2MSFTNGP04.phx.gbl...
>> Daniel
>> As Geoff indicates, you should not put the column inside a function.
>> However, the statement that the column must be evaluated against a
>> constant is a little too strict. The column should be all by itself, but
>> can be compared to an expression, including one containing the dateadd
>> function, as Geoff uses in his example.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
>> news:OuVHc9L9HHA.4200@.TK2MSFTNGP04.phx.gbl...
>> Ouch.
>> That will result in a table scan. Never put the column inside the
>> function. Always evaluate the column against a constant.
>>
>> Try this
>> SELECT *
>> FROM MyTable
>> WHERE MyDateColumn >= DATEadd( GETDATE(), -2)
>> That will be for the last 48 hours. If you want to have since the
>> second midnight previous, that is a different query.
>> --
>> Geoff N. Hiten
>> Senior SQL Infrastructure Consultant
>> Microsoft SQL Server MVP
>>
>>
>> "Daniel Jameson" <danjam47@.newsgroup.nospam> wrote in message
>> news:Oc5hPzL9HHA.4180@.TK2MSFTNGP05.phx.gbl...
>> SELECT *
>> FROM MyTable
>> WHERE DATEDIFF(day, MyDateColumn, GETDATE()) <= 2
>> --
>> Thank you,
>> Daniel Jameson
>> SQL Server DBA
>> Children's Oncology Group
>> www.childrensoncologygroup.org
>>
>> <gouqizi.lvcha@.gmail.com> wrote in message
>> news:1189544898.368595.123490@.e34g2000pro.googlegroups.com...
>>I have table which have a day-to-day information. How to use sql to
>> retrive the last two days information? Thanks.
>>
>>
>>
>|||<gouqizi.lvcha@.gmail.com> wrote in message
news:1189544898.368595.123490@.e34g2000pro.googlegroups.com...
>I have table which have a day-to-day information. How to use sql to
> retrive the last two days information? Thanks.
>
"Last two" meaning latest two days?
SELECT ...
FROM tbl
WHERE dt IN
(SELECT DISTINCT TOP 2 dt
FROM tbl
ORDER BY dt DESC);
Or latest two rows?
SELECT TOP 2 ...
FROM tbl
ORDER BY dt DESC;
Or yesterday and today?
SELECT ...
FROM tbl
WHERE dt >= '20070910' AND dt < '20070912';
--
David Portas|||David,
Your first query works only if date-only timestamps are stored in dt. If dt
has transactional timestamps with time, then you'll just get the two most
recent rows.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:3JadnUmUnONrgXrbnZ2dnUVZ8tWnnZ2d@.giganews.com...
> <gouqizi.lvcha@.gmail.com> wrote in message
> news:1189544898.368595.123490@.e34g2000pro.googlegroups.com...
>>I have table which have a day-to-day information. How to use sql to
>> retrive the last two days information? Thanks.
> "Last two" meaning latest two days?
> SELECT ...
> FROM tbl
> WHERE dt IN
> (SELECT DISTINCT TOP 2 dt
> FROM tbl
> ORDER BY dt DESC);
> Or latest two rows?
> SELECT TOP 2 ...
> FROM tbl
> ORDER BY dt DESC;
> Or yesterday and today?
> SELECT ...
> FROM tbl
> WHERE dt >= '20070910' AND dt < '20070912';
> --
> David Portas
>|||Kalen is correct, as usual. The Dateadd(day,GETDATE()-,-2) expression is
evaluated once for the entire query rather than once per row. This means
that the comparison for the row match is an index entry (assuming the table
is properly indexed for this query) compared to a constant. If the column
is inside a function, ALL rows (or index entries) must be readm, run through
the function, and compared.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eCV56CM9HHA.4752@.TK2MSFTNGP04.phx.gbl...
> Daniel
> As Geoff indicates, you should not put the column inside a function.
> However, the statement that the column must be evaluated against a
> constant is a little too strict. The column should be all by itself, but
> can be compared to an expression, including one containing the dateadd
> function, as Geoff uses in his example.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:OuVHc9L9HHA.4200@.TK2MSFTNGP04.phx.gbl...
>> Ouch.
>> That will result in a table scan. Never put the column inside the
>> function. Always evaluate the column against a constant.
>>
>> Try this
>> SELECT *
>> FROM MyTable
>> WHERE MyDateColumn >= DATEadd( GETDATE(), -2)
>> That will be for the last 48 hours. If you want to have since the second
>> midnight previous, that is a different query.
>> --
>> Geoff N. Hiten
>> Senior SQL Infrastructure Consultant
>> Microsoft SQL Server MVP
>>
>>
>> "Daniel Jameson" <danjam47@.newsgroup.nospam> wrote in message
>> news:Oc5hPzL9HHA.4180@.TK2MSFTNGP05.phx.gbl...
>> SELECT *
>> FROM MyTable
>> WHERE DATEDIFF(day, MyDateColumn, GETDATE()) <= 2
>> --
>> Thank you,
>> Daniel Jameson
>> SQL Server DBA
>> Children's Oncology Group
>> www.childrensoncologygroup.org
>>
>> <gouqizi.lvcha@.gmail.com> wrote in message
>> news:1189544898.368595.123490@.e34g2000pro.googlegroups.com...
>>I have table which have a day-to-day information. How to use sql to
>> retrive the last two days information? Thanks.
>>
>>
>|||"Daniel Jameson" <danjam47@.newsgroup.nospam> wrote in message
news:ObC5QoM9HHA.4752@.TK2MSFTNGP04.phx.gbl...
> David,
> Your first query works only if date-only timestamps are stored in dt. If
> dt has transactional timestamps with time, then you'll just get the two
> most recent rows.
>
Good catch Daniel. Thanks. In SQL Server 2005 the DENSE_RANK function can
solve that one nicely.
Quota queries are much harder than they seem. You can usually bet that when
someone says "Top N of X" they haven't considered all the possible meanings
that phrase could have.
CREATE TABLE tbl (i INT PRIMARY KEY, dt DATETIME NOT NULL);
INSERT INTO tbl VALUES (1,'2000-01-01T15:00:00');
INSERT INTO tbl VALUES (2,'2000-01-01T15:00:00');
INSERT INTO tbl VALUES (3,'2000-01-01T16:00:00');
INSERT INTO tbl VALUES (4,'2000-01-02T20:00:00');
INSERT INTO tbl VALUES (5,'2000-01-02T21:00:00');
INSERT INTO tbl VALUES (6,'2000-01-03T22:00:00');
INSERT INTO tbl VALUES (7,'2000-01-03T23:00:00');
SELECT i,dt
FROM
(SELECT i,dt, DENSE_RANK()
OVER (ORDER BY DATEDIFF(DAY,'20000101',dt) DESC) AS rnk
FROM tbl) t
WHERE rnk <=2;
i dt
-- --
6 2000-01-03 22:00:00.000
7 2000-01-03 23:00:00.000
4 2000-01-02 20:00:00.000
5 2000-01-02 21:00:00.000
(4 row(s) affected)
--
David Portas|||On Tue, 11 Sep 2007 15:11:25 -0700, Kalen Delaney wrote:
>Daniel
>As Geoff indicates, you should not put the column inside a function.
>However, the statement that the column must be evaluated against a constant
>is a little too strict. The column should be all by itself, but can be
>compared to an expression, including one containing the dateadd function, as
>Geoff uses in his example.
... just as long as said expression does not include any columns from
the same table as the indexed column.
</nitpick>
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis