Set variable date for Query used for daily report

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Set variable date for Query used for daily report

Author

Topic

SQLN00b11
Starting Member

6 Posts

Posted - 2011-03-08 : 17:39:26

Hi all,
I am using T-sql need to run a daily report using a query which will return stats between 00:00:00 amd 23:59:59 the day before. I know I need to somehow use today's date and -1 to get yesterday's stats. I think I need to use DateAdd and DatePart. I haven't come across any books or online resources that has a simple answer.

Could someone show me how to implement the date parameters correctly so the report can be run every day using the same time period?

Below is the query I am working with but the dates are fixed:


DECLARE @Starttime datetime

DECLARE @Endtime datetime

 
Set @Starttime = '2011-02-11 00:00:00'

Set @Endtime = '2011-02-11 23:59:59'

SELECT *

FROM P_call

WHERE (starttime between @starttime and @endtime)

order by NU desc

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-08 : 17:43:58

[code]
where starttime >= dateadd(day, datediff(day, 0, getdate()), -1)
and starttime < dateadd(day, datediff(day, 0, getdate()), 0)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

SQLN00b11
Starting Member

6 Posts

Posted - 2011-03-08 : 18:10:42

Hi khtan,

How do I incorporate the time period into your code so that it covers the 24 hour period of the whole day?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-08 : 21:12:59

quote:
Originally posted by SQLN00b11

Hi khtan,

How do I incorporate the time period into your code so that it covers the 24 hour period of the whole day?

what do you mean ?

The query i posted is for the whole of yesterday date.
If today is 2011-03-09, it will checks for time that is
- greater and equal to '2011-03-08 00:00'
AND
- less than '2011-03-09 00:00'

KH
[spoiler]Time is always against us[/spoiler]

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-03-09 : 00:07:17

CREATE VIEW Yesterday (..)
AS
SELECT * -- write our the list!!
FROM P_call
WHERE CAST (p_start_time AS DATE)
= DATEADD (DAY, CAST(CURRENT_TIMESTAMP AS DATE), -1 ));

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources