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 issue in a SQL 2005 scheduled job

I have a job in SQL 2005 that when it runs works fine if I hard code the Makedate, what I need it to do is have the Makedate equal todays date minus one day...I came up with the code below, but that does seem to work...any thoughts.

INSERT INTO abcTransaction
(
Payee,
Payment,
AccountNumber
)
SELECT
'Credit',
SUM(Rebate * Quantity),
A.AccountNumber
FROM
Account A
INNER JOIN
abdTrades T on A.AccountNumber = T.AccountNumber
Where MakeDate = getDate() - 1

GROUP BY
A.AccountNumber

Looks good. What is the error message?|||

Try:

Where MakeDate = DATEADD(day, -1, getDate())

Keep in mind, you are just subtracting one day - 24 hours. So if the underlying value for your date is 1/10/2007 15:00, then the minus one day produces 1/9/1007 15:00. So if you really want for the whole previous day try:

Where MakeDate = CAST(MONTH(DATEADD(day, - 1, GETDATE())) AS varchar) + '/' + CAST(DAY(DATEADD(day, - 1, GETDATE())) AS varchar) + '/' + CAST(YEAR(DATEADD(day, - 1, GETDATE())) AS varchar))

This is kind of messy and Ihave not found a better way. I usually wrap it into a SQL function.

|||

cloris:

Keep in mind, you are just subtracting one day - 24 hours. So if the underlying value for your date is 1/10/2007 15:00, then the minus one day produces 1/9/1007 15:00. So if you really want for the whole previous day try:

Where MakeDate = CAST(MONTH(DATEADD(day, - 1, GETDATE())) AS varchar) + '/' + CAST(DAY(DATEADD(day, - 1, GETDATE())) AS varchar) + '/' + CAST(YEAR(DATEADD(day, - 1, GETDATE())) AS varchar))

This is kind of messy and Ihave not found a better way. I usually wrap it into a SQL function.

If you need for entire previous day you can do it like this:

Where MakeDate >= convert(Varchar, Getdate() -1, 101) And MakeDate < convert(Varchar, Getdate() , 101)

|||

Will try this as it does need to be for the entire previous day, not a 24 hour cycle.

|||Much cleaner... I am filing this one away.