select Count(*) with date with sql server 6.5 and 7
| Author |
Message |
|
CHAPUZOT FRANCOI
#1 / 5
|
 select Count(*) with date with sql server 6.5 and 7 I'm writing queries in VB that will be executed with SQL Server 6.5 and I'm new to SQL Server. I precisely want to count how many records have been created since the
past twelve months, and group the results by month.
With MS Access I could write :
SELECT COUNT(*) FROM tablename WHERE ...
AND tablename.datefieldname >= Dateadd('m',-13,Now())
AND tablename.datefieldname <= Dateadd('m',-1,Now())
GROUP BY MONTH(tablename.datefieldname), YEAR(tablename.datefieldname)
But it doesn't work in SQL for SQL SERVER.
I know that I have to use functions like Datepart instead of Year or
Month, and Getdate() instead of Now(). Am I wright ?
But there is also the problem of format, and the problem of making a
difference between two dates.
So I don't know how to write this type of queries.
Am I the only one one person having this problem ?
If you can help me solving this problem, and finding information on, or
examples of SQL Server queries, thanks a lot.
David.
|
| Fri, 09 Aug 2002 03:00:00 GMT |
|
 |
|
Mario Alcara
#2 / 5
|
select Count(*) with date with sql server 6.5 and 7 You need to use month instead of 'm' and getdate() instead of now(). Thats all.
Quote: > I'm writing queries in VB that will be executed with SQL Server 6.5 and I'm > new to SQL Server. > I precisely want to count how many records have been created since the
> past twelve months, and group the results by month.
> With MS Access I could write :
> SELECT COUNT(*) FROM tablename WHERE ...
> AND tablename.datefieldname >= Dateadd('m',-13,Now())
> AND tablename.datefieldname <= Dateadd('m',-1,Now())
> GROUP BY MONTH(tablename.datefieldname), YEAR(tablename.datefieldname)
> But it doesn't work in SQL for SQL SERVER.
> I know that I have to use functions like Datepart instead of Year or
> Month, and Getdate() instead of Now(). Am I wright ?
> But there is also the problem of format, and the problem of making a
> difference between two dates.
> So I don't know how to write this type of queries.
> Am I the only one one person having this problem ?
> If you can help me solving this problem, and finding information on, or
> examples of SQL Server queries, thanks a lot.
> David.
|
| Fri, 09 Aug 2002 03:00:00 GMT |
|
|
|
Suresh
#3 / 5
|
select Count(*) with date with sql server 6.5 and 7 Hi David, You were pretty close. Try this one. SELECT COUNT(*) FROM tablename WHERE ...
AND tablename.datefieldname >= Dateadd(mm,-13,getdate())
AND tablename.datefieldname <= Dateadd(mm,-1,getdate())
GROUP BY datepart(mm,tablename.datefieldname),
datepart(yyyy,tablename.datefieldname)
Thanks
Suresh Krishnan
Quote: > I'm writing queries in VB that will be executed with SQL Server 6.5 and I'm > new to SQL Server. > I precisely want to count how many records have been created since the
> past twelve months, and group the results by month.
> With MS Access I could write :
> SELECT COUNT(*) FROM tablename WHERE ...
> AND tablename.datefieldname >= Dateadd('m',-13,Now())
> AND tablename.datefieldname <= Dateadd('m',-1,Now())
> GROUP BY MONTH(tablename.datefieldname), YEAR(tablename.datefieldname)
> But it doesn't work in SQL for SQL SERVER.
> I know that I have to use functions like Datepart instead of Year or
> Month, and Getdate() instead of Now(). Am I wright ?
> But there is also the problem of format, and the problem of making a
> difference between two dates.
> So I don't know how to write this type of queries.
> Am I the only one one person having this problem ?
> If you can help me solving this problem, and finding information on, or
> examples of SQL Server queries, thanks a lot.
> David.
|
| Fri, 09 Aug 2002 03:00:00 GMT |
|
|
|
Kalen Delane
#4 / 5
|
select Count(*) with date with sql server 6.5 and 7 When using Dateadd, the first argument is not in parens... and you have the value slightly wrong. You can use: dateadd(mm, -13,getdate())
and GROUP BY datepart(mm, datefield), datepart(yy, datefield)
BTW, SQL 7 has a MONTH() and YEAR() function.
HTH
--
Kalen Delaney
MCSE, SQL Server MCT, MVP
www.InsideSQLServer.com
Feed Someone for Free Today:
www.TheHungerSite.com
Quote: > I'm writing queries in VB that will be executed with SQL Server 6.5 and I'm > new to SQL Server. > I precisely want to count how many records have been created since the
> past twelve months, and group the results by month.
> With MS Access I could write :
> SELECT COUNT(*) FROM tablename WHERE ...
> AND tablename.datefieldname >= Dateadd('m',-13,Now())
> AND tablename.datefieldname <= Dateadd('m',-1,Now())
> GROUP BY MONTH(tablename.datefieldname), YEAR(tablename.datefieldname)
> But it doesn't work in SQL for SQL SERVER.
> I know that I have to use functions like Datepart instead of Year or
> Month, and Getdate() instead of Now(). Am I wright ?
> But there is also the problem of format, and the problem of making a
> difference between two dates.
> So I don't know how to write this type of queries.
> Am I the only one one person having this problem ?
> If you can help me solving this problem, and finding information on, or
> examples of SQL Server queries, thanks a lot.
> David.
|
| Fri, 09 Aug 2002 03:00:00 GMT |
|
|
|
Joseph Ierull
#5 / 5
|
select Count(*) with date with sql server 6.5 and 7 Look at YearPart and MonthPart functions in SQL server.
Quote: > I'm writing queries in VB that will be executed with SQL Server 6.5 and I'm > new to SQL Server. > I precisely want to count how many records have been created since the
> past twelve months, and group the results by month.
> With MS Access I could write :
> SELECT COUNT(*) FROM tablename WHERE ...
> AND tablename.datefieldname >= Dateadd('m',-13,Now())
> AND tablename.datefieldname <= Dateadd('m',-1,Now())
> GROUP BY MONTH(tablename.datefieldname), YEAR(tablename.datefieldname)
> But it doesn't work in SQL for SQL SERVER.
> I know that I have to use functions like Datepart instead of Year or
> Month, and Getdate() instead of Now(). Am I wright ?
> But there is also the problem of format, and the problem of making a
> difference between two dates.
> So I don't know how to write this type of queries.
> Am I the only one one person having this problem ?
> If you can help me solving this problem, and finding information on, or
> examples of SQL Server queries, thanks a lot.
> David.
|
| Sat, 24 Aug 2002 03:00:00 GMT |
|
| |
|
