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.