query on day's transactions

Let me preface this by saying that i'm not a programmer.
I'm trying to setup a job that runs a query every night at 7pm that will
pull all transactions with the date reported that equals the system date.
What identifier would I use so that it pulled transactions based on the the
system date? My query is below, but as you see it has a yesterday's date.
select [CaseLog].[DateReported], [CaseLog].[CaseNbr]
from [CaseLog]
where [CaseLog].[DateReported]='2005-09-26 00:00:00'
order by [CaseLog].[DateReported], [CaseLog].[CaseNbr]
Thanks in advance
On Mon, 26 Sep 2005 13:45:03 -0700, dhamric wrote:

>Let me preface this by saying that i'm not a programmer.
>I'm trying to setup a job that runs a query every night at 7pm that will
>pull all transactions with the date reported that equals the system date.
>What identifier would I use so that it pulled transactions based on the the
>system date? My query is below, but as you see it has a yesterday's date.
>select [CaseLog].[DateReported], [CaseLog].[CaseNbr]
>from [CaseLog]
>where [CaseLog].[DateReported]='2005-09-26 00:00:00'
>order by [CaseLog].[DateReported], [CaseLog].[CaseNbr]
>Thanks in advance
Hi dhamric,
You could use
SELECT CaseLog.DateReported, CaseLog.CaseNbr
FROM CaseLog
WHERE CaseLog.DateReported = DATEADD(day,
DATEDIFF(day, 0, getdate()), 0)
ORDER BY CaseLog.DateReported, CaseLog.CaseNbr
But if the DateReported column can be stored with a time portion other
than midnight, you should use this instead:
SELECT CaseLog.DateReported, CaseLog.CaseNbr
FROM CaseLog
WHERE CaseLog.DateReported >= DATEADD(day,
DATEDIFF(day, 0, getdate()), 0)
AND CaseLog.DateReported < DATEADD(day,
DATEDIFF(day, 0, getdate()), 1)
ORDER BY CaseLog.DateReported, CaseLog.CaseNbr
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Very helpful. Thank you!
"Hugo Kornelis" wrote:

> On Mon, 26 Sep 2005 13:45:03 -0700, dhamric wrote:
>
> Hi dhamric,
> You could use
> SELECT CaseLog.DateReported, CaseLog.CaseNbr
> FROM CaseLog
> WHERE CaseLog.DateReported = DATEADD(day,
> DATEDIFF(day, 0, getdate()), 0)
> ORDER BY CaseLog.DateReported, CaseLog.CaseNbr
> But if the DateReported column can be stored with a time portion other
> than midnight, you should use this instead:
> SELECT CaseLog.DateReported, CaseLog.CaseNbr
> FROM CaseLog
> WHERE CaseLog.DateReported >= DATEADD(day,
> DATEDIFF(day, 0, getdate()), 0)
> AND CaseLog.DateReported < DATEADD(day,
> DATEDIFF(day, 0, getdate()), 1)
> ORDER BY CaseLog.DateReported, CaseLog.CaseNbr
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>