Date question

Is it possible with SQL date functions to have a recurring query that runs
on say the 15th of every month (that part, I know, is easily scheduled) that
will pull the following month in it's entirety? For instance, I want an
automated script to run every month on the 15th that will give me every one
that purchased a specific product on nay day of the following month. Thanks
for you help.
WillieWillie
what do you want to do with the data once you have it? (ie insert into
a talbe)
Also let me make sure i understand what you are looking for;
you want to know for a product that was order a month in the past on
the day you run this (ie jan/15/2006 would get data from Dec/15/2005).
Is that correct?|||Asuming that you want to get a report of items purchased in the PREVIOUS
month executed on the 15th, here is a suggestion:
-- BEGIN SCRIPT
set nocount on
declare @.date datetime
-- Creating a Item Purchased Table
declare @.Items table(Item varchar(50), PurchDate datetime)
-- Populating the tables
insert into @.items
values ('Item1', '12/15/2005')
insert into @.items
values ('Item2', '12/15/2005')
insert into @.items
values ('Item1', '12/18/2005')
insert into @.items
values ('Item3', '12/17/2005')
insert into @.items
values ('Item1', '12/29/2005')
insert into @.items
values ('Item4', '12/15/2005')
insert into @.items
values ('Item1', '01/14/2006')
set @.date = '01/15/2006' -- getdate() on the real script
-- Only executes on the 15th date of the month
if (select DATEPART(dd, @.date)) = 15
begin
select Item
, PurchDate
from @.Items
where PurchDate between DATEADD(day, -30, @.date) and (@.date)
end
set nocount off
except for the table and the insert statements (I just created them as an
example) you can add this script in to run every day, however it will only
execute on the 15th of the month and it will go back 30 days to get a report
of the items purchased and the correspondent dates.
Let me know if it helps.
"Willie Bodger" wrote:

> Is it possible with SQL date functions to have a recurring query that runs
> on say the 15th of every month (that part, I know, is easily scheduled) th
at
> will pull the following month in it's entirety? For instance, I want an
> automated script to run every month on the 15th that will give me every on
e
> that purchased a specific product on nay day of the following month. Thank
s
> for you help.
> Willie
>
>sql

Date query help

I have a table with a date_created and date_expired and I want to look
at the 15th of each month and every month since the system started and
count up how many accounts were active at each of those points in
time.
Will have to assume that if there's an expiry date bigger than today
then that account was active at each of those points in time.

Can someone pelase tell me how can I do that?

Thanks a lotOn 19 Mar 2007 10:14:33 -0700, Giorgio wrote:

Quote:

Originally Posted by

>I have a table with a date_created and date_expired and I want to look
>at the 15th of each month and every month since the system started and
>count up how many accounts were active at each of those points in
>time.
>Will have to assume that if there's an expiry date bigger than today
>then that account was active at each of those points in time.
>
>Can someone pelase tell me how can I do that?
>
>Thanks a lot

Hi Giorgio,

This question has already been answered in
microsoft.public.sqlserver.programming. In the future, please do not
multipost your questions, as it might cause people to waste time
figuring out an answer to a question that's already been answered
elsewhere.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Date & time formats

I'd like to be able to display date/time in a format appropriate for the location of the viewer. (i.e., for March 15th the date would be 3/15/2006 for US viewers and 15/3/2006 for UK viewers.) What would be the simplest way of implementing this?

Thanks!

Try setting the Report Language to an expression that equals the language of the user running the report. The expression looks like this. I haven't tested this so let me know if it works.

=User!Language