Reports and GetDate

I am doing a monthly report to export to an excel Doc from SQL 2000
using views.
With my design it works great until it is the first of the month. As the
report always
checks from yesterday to the first of the current month. One view is below.
The date statement checks between the first of month and current date -1
Can anyone help with with this?
Mike
CREATE VIEW dbo.GUTUR
AS
SELECT COUNT(*) AS GUTUR
FROM dbo.Chev
WHERE ([Deal Date] BETWEEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)
AND GETDATE() - 1) AND ([N/U] = 'U') AND (([Sales Type] = 'R') or ([Sales
Type] = '3'))Hi Mike,
If I have understood right, when the current date is the first of month, the
WHERE clause doesn't apply in your case.
([Deal Date] BETWEEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)
AND GETDATE() - 1)
For Ex:
Based on the above Where clause mentioned by you, when current date is Jan
1st, it becomes,
[Deal Date] BETWEEN 2005-01-01 00:00:00.000 AND 2004-12-31 00:00:00.000
because of which it doesn't return any rows.
BTW, when current date is Jan 1st, you should not have any data, from your
given sql statement. If it is otherwise, please let me know.
--
Thanks
GYK|||You can try this :
CREATE VIEW dbo.GUTUR
AS
SELECT COUNT(*) AS GUTUR
FROM dbo.Chev
WHERE ([Deal Date] BETWEEN
convert(datetime, convert(varchar(11), getdate(), 101)) - datepart(dd,
getdate()) + 1 AND convert(datetime, convert(varchar(11), getdate() - 1,
101))
AND ([N/U] = 'U') AND (([Sales Type] = 'R') or ([Sales Type] = '3'))
You will also have to remove the time portion from the date to get the
correct result.
-Nags
"Mike Lander" <landers@.lanlinecomputers.com> wrote in message
news:crc0qv$s7c$1@.nnrp.atgi.net...
> I am doing a monthly report to export to an excel Doc from SQL 2000
> using views.
> With my design it works great until it is the first of the month. As the
> report always
> checks from yesterday to the first of the current month. One view is
below.
> The date statement checks between the first of month and current
date -1
> Can anyone help with with this?
>
> Mike
> CREATE VIEW dbo.GUTUR
> AS
> SELECT COUNT(*) AS GUTUR
> FROM dbo.Chev
> WHERE ([Deal Date] BETWEEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)
> AND GETDATE() - 1) AND ([N/U] = 'U') AND (([Sales Type] = 'R') or
([Sales
> Type] = '3'))
>
>
>|||On Mon, 3 Jan 2005 09:53:34 -0800, Mike Lander wrote:
> I am doing a monthly report to export to an excel Doc from SQL 2000
>using views.
> With my design it works great until it is the first of the month. As the
>report always
>checks from yesterday to the first of the current month. One view is below.
> The date statement checks between the first of month and current date -1
> Can anyone help with with this?
Hi Mike,
I understand what yoou write, but I don't see what you want. What exactly
would you expect? You say you want output from the first day of the
current month until yesterday. If you run this report on Jan 1st, this
would mean you want output from Jan 1 2005 until Dec 31 2004, producing an
empty report, or course.
I believe that your view will do just that: produce an empty report on the
first of any month, produce a report about only the first day of the month
when run on the second day, etc.
Were you expecting something else on the first day of the month? If so,
what output did you want to get?
http://www.aspfaq.com/5006
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)