Monthly Reports - Date Format

I have a report that charts our weekly sales and monthly sales. I use the
following SQL statement to reflect 7 days or 30 days, which works but not
properly. I'd like for my report to run from Sunday to Sunday for the 7-day
report, and from the 1st of the month to the end of the month, regardless of
the number of days in the month. How do I modfify my statement to do this?
Here's my SQL statement for the 30-day or monthly report:
SELECT receipt_date, SUM(total_amt) AS Total_AMT, receipt_no, stamp, COUNT
(*) AS TOT
FROM Sysadm.receipt
WHERE (receipt_date BETWEEN CONVERT(datetime, CONVERT(varchar, DATEADD(day,
-30, GETDATE()), 101), 101) AND CONVERT(varchar, DATEADD(day, 0, GETDATE()),
101), 101))
GROUP BY receipt_date, receipt_no, stamp
ORDER BY receipt_date
--
EnchantnetI used the following statement to run a report for the previous week, from
Saturday to Friday:
CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 - DATEPART(dw, GETDATE()),
101), 101) AS StartDt,
CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 1 - DATEPART(dw, GETDATE()),
101), 101) AS EndDt
You could probably use a variation of this for your monthly report. I hope
this helps!
"Enchantnet" wrote:
> I have a report that charts our weekly sales and monthly sales. I use the
> following SQL statement to reflect 7 days or 30 days, which works but not
> properly. I'd like for my report to run from Sunday to Sunday for the 7-day
> report, and from the 1st of the month to the end of the month, regardless of
> the number of days in the month. How do I modfify my statement to do this?
> Here's my SQL statement for the 30-day or monthly report:
> SELECT receipt_date, SUM(total_amt) AS Total_AMT, receipt_no, stamp, COUNT
> (*) AS TOT
> FROM Sysadm.receipt
> WHERE (receipt_date BETWEEN CONVERT(datetime, CONVERT(varchar, DATEADD(day,
> -30, GETDATE()), 101), 101) AND CONVERT(varchar, DATEADD(day, 0, GETDATE()),
> 101), 101))
> GROUP BY receipt_date, receipt_no, stamp
> ORDER BY receipt_date
> --
> Enchantnet|||Hey DAW,
Thanks for the code. However, I'm a newbie at this sort of stuff and I get
the following error when I entered your code: "ADO error: Incorrect syntax
near the keyword 'AS'. Statement(s) could not be prepared. Deferred prepare
could not be completed." Any idea? Do I need to include or define the
StartDt and EndDt elsewhere?
--
Enchantnet
"daw" wrote:
> I used the following statement to run a report for the previous week, from
> Saturday to Friday:
> CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 - DATEPART(dw, GETDATE()),
> 101), 101) AS StartDt,
> CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 1 - DATEPART(dw, GETDATE()),
> 101), 101) AS EndDt
> You could probably use a variation of this for your monthly report. I hope
> this helps!
> "Enchantnet" wrote:
> > I have a report that charts our weekly sales and monthly sales. I use the
> > following SQL statement to reflect 7 days or 30 days, which works but not
> > properly. I'd like for my report to run from Sunday to Sunday for the 7-day
> > report, and from the 1st of the month to the end of the month, regardless of
> > the number of days in the month. How do I modfify my statement to do this?
> > Here's my SQL statement for the 30-day or monthly report:
> >
> > SELECT receipt_date, SUM(total_amt) AS Total_AMT, receipt_no, stamp, COUNT
> > (*) AS TOT
> > FROM Sysadm.receipt
> > WHERE (receipt_date BETWEEN CONVERT(datetime, CONVERT(varchar, DATEADD(day,
> > -30, GETDATE()), 101), 101) AND CONVERT(varchar, DATEADD(day, 0, GETDATE()),
> > 101), 101))
> > GROUP BY receipt_date, receipt_no, stamp
> > ORDER BY receipt_date
> > --
> > Enchantnet|||I'm not sure what that particular error means, but your statement should look
something like:
select CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 - DATEPART(dw,
GETDATE()), 101), 101) AS StartDt, CONVERT(datetime, CONVERT(nvarchar,
GETDATE() - 1 - DATEPART(dw, GETDATE()), 101), 101) AS EndDt
from ....
"Enchantnet" wrote:
> Hey DAW,
> Thanks for the code. However, I'm a newbie at this sort of stuff and I get
> the following error when I entered your code: "ADO error: Incorrect syntax
> near the keyword 'AS'. Statement(s) could not be prepared. Deferred prepare
> could not be completed." Any idea? Do I need to include or define the
> StartDt and EndDt elsewhere?
> --
> Enchantnet
>
> "daw" wrote:
> > I used the following statement to run a report for the previous week, from
> > Saturday to Friday:
> > CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 - DATEPART(dw, GETDATE()),
> > 101), 101) AS StartDt,
> > CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 1 - DATEPART(dw, GETDATE()),
> > 101), 101) AS EndDt
> >
> > You could probably use a variation of this for your monthly report. I hope
> > this helps!
> >
> > "Enchantnet" wrote:
> >
> > > I have a report that charts our weekly sales and monthly sales. I use the
> > > following SQL statement to reflect 7 days or 30 days, which works but not
> > > properly. I'd like for my report to run from Sunday to Sunday for the 7-day
> > > report, and from the 1st of the month to the end of the month, regardless of
> > > the number of days in the month. How do I modfify my statement to do this?
> > > Here's my SQL statement for the 30-day or monthly report:
> > >
> > > SELECT receipt_date, SUM(total_amt) AS Total_AMT, receipt_no, stamp, COUNT
> > > (*) AS TOT
> > > FROM Sysadm.receipt
> > > WHERE (receipt_date BETWEEN CONVERT(datetime, CONVERT(varchar, DATEADD(day,
> > > -30, GETDATE()), 101), 101) AND CONVERT(varchar, DATEADD(day, 0, GETDATE()),
> > > 101), 101))
> > > GROUP BY receipt_date, receipt_no, stamp
> > > ORDER BY receipt_date
> > > --
> > > Enchantnet|||Sorry, I had that wrong as far as what you need. Try this:
select ....
from...
where date between CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 -
DATEPART(dw, GETDATE()), 101), 101) AND CONVERT(datetime, CONVERT(nvarchar,
GETDATE() - 1 - DATEPART(dw, GETDATE()), 101), 101)
"daw" wrote:
> I'm not sure what that particular error means, but your statement should look
> something like:
> select CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 - DATEPART(dw,
> GETDATE()), 101), 101) AS StartDt, CONVERT(datetime, CONVERT(nvarchar,
> GETDATE() - 1 - DATEPART(dw, GETDATE()), 101), 101) AS EndDt
> from ....
> "Enchantnet" wrote:
> > Hey DAW,
> >
> > Thanks for the code. However, I'm a newbie at this sort of stuff and I get
> > the following error when I entered your code: "ADO error: Incorrect syntax
> > near the keyword 'AS'. Statement(s) could not be prepared. Deferred prepare
> > could not be completed." Any idea? Do I need to include or define the
> > StartDt and EndDt elsewhere?
> > --
> > Enchantnet
> >
> >
> > "daw" wrote:
> >
> > > I used the following statement to run a report for the previous week, from
> > > Saturday to Friday:
> > > CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 - DATEPART(dw, GETDATE()),
> > > 101), 101) AS StartDt,
> > > CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 1 - DATEPART(dw, GETDATE()),
> > > 101), 101) AS EndDt
> > >
> > > You could probably use a variation of this for your monthly report. I hope
> > > this helps!
> > >
> > > "Enchantnet" wrote:
> > >
> > > > I have a report that charts our weekly sales and monthly sales. I use the
> > > > following SQL statement to reflect 7 days or 30 days, which works but not
> > > > properly. I'd like for my report to run from Sunday to Sunday for the 7-day
> > > > report, and from the 1st of the month to the end of the month, regardless of
> > > > the number of days in the month. How do I modfify my statement to do this?
> > > > Here's my SQL statement for the 30-day or monthly report:
> > > >
> > > > SELECT receipt_date, SUM(total_amt) AS Total_AMT, receipt_no, stamp, COUNT
> > > > (*) AS TOT
> > > > FROM Sysadm.receipt
> > > > WHERE (receipt_date BETWEEN CONVERT(datetime, CONVERT(varchar, DATEADD(day,
> > > > -30, GETDATE()), 101), 101) AND CONVERT(varchar, DATEADD(day, 0, GETDATE()),
> > > > 101), 101))
> > > > GROUP BY receipt_date, receipt_no, stamp
> > > > ORDER BY receipt_date
> > > > --
> > > > Enchantnet|||Perfect! Thank u, Thank u, Thank u!!!!!
--
Enchantnet
"daw" wrote:
> Sorry, I had that wrong as far as what you need. Try this:
> select ....
> from...
> where date between CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 -
> DATEPART(dw, GETDATE()), 101), 101) AND CONVERT(datetime, CONVERT(nvarchar,
> GETDATE() - 1 - DATEPART(dw, GETDATE()), 101), 101)
> "daw" wrote:
> > I'm not sure what that particular error means, but your statement should look
> > something like:
> >
> > select CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 - DATEPART(dw,
> > GETDATE()), 101), 101) AS StartDt, CONVERT(datetime, CONVERT(nvarchar,
> > GETDATE() - 1 - DATEPART(dw, GETDATE()), 101), 101) AS EndDt
> > from ....
> >
> > "Enchantnet" wrote:
> >
> > > Hey DAW,
> > >
> > > Thanks for the code. However, I'm a newbie at this sort of stuff and I get
> > > the following error when I entered your code: "ADO error: Incorrect syntax
> > > near the keyword 'AS'. Statement(s) could not be prepared. Deferred prepare
> > > could not be completed." Any idea? Do I need to include or define the
> > > StartDt and EndDt elsewhere?
> > > --
> > > Enchantnet
> > >
> > >
> > > "daw" wrote:
> > >
> > > > I used the following statement to run a report for the previous week, from
> > > > Saturday to Friday:
> > > > CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 7 - DATEPART(dw, GETDATE()),
> > > > 101), 101) AS StartDt,
> > > > CONVERT(datetime, CONVERT(nvarchar, GETDATE() - 1 - DATEPART(dw, GETDATE()),
> > > > 101), 101) AS EndDt
> > > >
> > > > You could probably use a variation of this for your monthly report. I hope
> > > > this helps!
> > > >
> > > > "Enchantnet" wrote:
> > > >
> > > > > I have a report that charts our weekly sales and monthly sales. I use the
> > > > > following SQL statement to reflect 7 days or 30 days, which works but not
> > > > > properly. I'd like for my report to run from Sunday to Sunday for the 7-day
> > > > > report, and from the 1st of the month to the end of the month, regardless of
> > > > > the number of days in the month. How do I modfify my statement to do this?
> > > > > Here's my SQL statement for the 30-day or monthly report:
> > > > >
> > > > > SELECT receipt_date, SUM(total_amt) AS Total_AMT, receipt_no, stamp, COUNT
> > > > > (*) AS TOT
> > > > > FROM Sysadm.receipt
> > > > > WHERE (receipt_date BETWEEN CONVERT(datetime, CONVERT(varchar, DATEADD(day,
> > > > > -30, GETDATE()), 101), 101) AND CONVERT(varchar, DATEADD(day, 0, GETDATE()),
> > > > > 101), 101))
> > > > > GROUP BY receipt_date, receipt_no, stamp
> > > > > ORDER BY receipt_date
> > > > > --
> > > > > Enchantnet