|
Home > Archive > MS SQL Server MSEQ > September 2005 > query on day's transactions
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
query on day's transactions
|
|
| dhamric 2005-09-26, 8:24 pm |
| 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].& #91;DateReported]='2
005-09-26 00:00:00'
order by [CaseLog].[DateReported], [CaseLog].[CaseNbr]
Thanks in advance
| |
| Hugo Kornelis 2005-09-26, 8:24 pm |
| 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].& #91;DateReported]='2
005-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)
| |
| dhamric 2005-09-26, 8:24 pm |
| 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)
>
|
|
|
|
|