Friday, March 30, 2012

Scripting a query to run daily.

I was just asked to script a Query that needs to run
daily and save the results as a file somewhere on the
network. The problem is that this report query needs to
be changed daily for example I need to run a report on
all scheduled apointments from the day before. My
question is this: Is there a system variable that I can
enter on the query that tells SQL to check the current
date on the system minus one so that the report is from
the day before (or Something similar). I have the query
and it runs as it should but I need to change the date
manually. If there's a solution to this problem please
let me know and how to insert it on the query.
Thanks.
getDate() gets the current system date. There are some date-manipulation
functions, such as dateAdd() (or something similar). Check out BOL
"Carlos Santos" <anonymous@.discussions.microsoft.com> wrote in message
news:2b15e01c4682c$b05b9c20$a501280a@.phx.gbl...
> I was just asked to script a Query that needs to run
> daily and save the results as a file somewhere on the
> network. The problem is that this report query needs to
> be changed daily for example I need to run a report on
> all scheduled apointments from the day before. My
> question is this: Is there a system variable that I can
> enter on the query that tells SQL to check the current
> date on the system minus one so that the report is from
> the day before (or Something similar). I have the query
> and it runs as it should but I need to change the date
> manually. If there's a solution to this problem please
> let me know and how to insert it on the query.
> Thanks.
|||You need to look at DATEADD function. This function allows you to do
datemath to calculate a new date. Also you might want to look into the
CONVERT function to get the output of the DATEADD function into a format to
compare with your data. Here is a query that uses these function to get
yesterdays date in the following format mm/dd/yyyy:
select * from yourtable where yourdatecolumn =
convert(char(10),dateadd(dd,-1,getdate()),101)
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Carlos Santos" <anonymous@.discussions.microsoft.com> wrote in message
news:2b15e01c4682c$b05b9c20$a501280a@.phx.gbl...
> I was just asked to script a Query that needs to run
> daily and save the results as a file somewhere on the
> network. The problem is that this report query needs to
> be changed daily for example I need to run a report on
> all scheduled apointments from the day before. My
> question is this: Is there a system variable that I can
> enter on the query that tells SQL to check the current
> date on the system minus one so that the report is from
> the day before (or Something similar). I have the query
> and it runs as it should but I need to change the date
> manually. If there's a solution to this problem please
> let me know and how to insert it on the query.
> Thanks.

No comments:

Post a Comment