Sort dates by month and year

[Guest]

Hi,
I have a table that contains "Operating_Dates" as a field in data/time
format and each date has a numer value in an adjacent field
The data in the table look as so:
1/5/2007 55
1/25/2007 11
1/29/2007 17
2/7/2007 35 and so on

I'm tryin to create a query that formats the individual dates into summed
month/year format so that the query returns a sum of all the integers for the
month. The ideal output would look as so:

Month Year Value
01 2007 83
02 2007 35

I've tried sorting the dates by month like Expr1: Month([Operating_Dates])
However, when i try to run the query i get an error saying "Inconsistent
datatypes: expected Number got Date". I thought the Month() function worked
with dates?
What should I do to get my ideal output from above?

Thanks!

 

[Michael Gramelspacher]

Hi,
I have a table that contains "Operating_Dates" as a field in data/time
format and each date has a numer value in an adjacent field
The data in the table look as so:
1/5/2007 55
1/25/2007 11
1/29/2007 17
2/7/2007 35 and so on

I'm tryin to create a query that formats the individual dates into summed
month/year format so that the query returns a sum of all the integers for the
month. The ideal output would look as so:

Month Year Value
01 2007 83
02 2007 35

I've tried sorting the dates by month like Expr1: Month([Operating_Dates])
However, when i try to run the query i get an error saying "Inconsistent
datatypes: expected Number got Date". I thought the Month() function worked
with dates?
What should I do to get my ideal output from above?

Thanks!

SELECT MONTH([operating_date]) AS [month],
YEAR([operating_date]) AS [year],
SUM(sometable.some_number) AS [number total]
FROM sometable
GROUP BY DATEADD("m",DATEDIFF("m",2,operating_date),2),
MONTH([operating_date]),YEAR([operating_date]);

 

[Guest]

SELECT Month([Operating_Dates]) AS TheMonth,
Year([Operating_Dates]) AS TheYear,
Sum(TheTable.Value) AS TheValue
FROM TheTable
GROUP BY Year([Operating_Dates]),
Month([Operating_Dates])
ORDER BY Year([Operating_Dates]),
Month([Operating_Dates]) ;

 

[Michael Gramelspacher]

Hi,
I have a table that contains "Operating_Dates" as a field in data/time
format and each date has a numer value in an adjacent field
The data in the table look as so:
1/5/2007 55
1/25/2007 11
1/29/2007 17
2/7/2007 35 and so on

I'm tryin to create a query that formats the individual dates into summed
month/year format so that the query returns a sum of all the integers for the
month. The ideal output would look as so:

Month Year Value
01 2007 83
02 2007 35

I've tried sorting the dates by month like Expr1: Month([Operating_Dates])
However, when i try to run the query i get an error saying "Inconsistent
datatypes: expected Number got Date". I thought the Month() function worked
with dates?
What should I do to get my ideal output from above?

Thanks!

SELECT MONTH([operating_date]) AS [month],
YEAR([operating_date]) AS [year],
SUM(sometable.some_number) AS [number total]
FROM sometable
GROUP BY DATEADD("m",DATEDIFF("m",2,operating_date),2),
MONTH([operating_date]),YEAR([operating_date]);

or this:

SELECT FORMAT(DATEADD("m",DATEDIFF("m",2,operating_date),2),
"m") AS [Month],
FORMAT(DATEADD("m",DATEDIFF("m",2,operating_date),2),
"yyyy") AS [Year],
SUM(sometable.some_number) AS [Number Total]
FROM sometable
GROUP BY DATEADD("m",DATEDIFF("m",2,operating_date),2);