mcrmg
asked on
SQL group by Date
Hi,
I have some data looks like the following:
F1 F2
somedata 2/1/2009
somedata 2/10/2009
somedata 2/15/2008
somedata 3/1/2009
somedata 3/18/2009
somedata 2/1/2009
How can I group find out the count for the records that are in the same month of the same year? So the outcome will look like this:
2/2008 1
2/2009 3
3/2009 2
thx
I have some data looks like the following:
F1 F2
somedata 2/1/2009
somedata 2/10/2009
somedata 2/15/2008
somedata 3/1/2009
somedata 3/18/2009
somedata 2/1/2009
How can I group find out the count for the records that are in the same month of the same year? So the outcome will look like this:
2/2008 1
2/2009 3
3/2009 2
thx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
is F2 varchar or datetime?
try this.
select RIGHT(CONVERT(VARCHAR(10), F2,103), 7),COUNT(F1)
from YourTable
group by RIGHT(CONVERT(VARCHAR(10),F2,103),7)
The above query works if your column F2 is of datetime. If it is a varchar type column, then try this query.
select RIGHT(CONVERT(VARCHAR(10), convert(datetime,F2),103), 7),COUNT(F1)
from YourTable
group by RIGHT(CONVERT(VARCHAR(10), convert(datetime,F2),103), 7)
ASKER
it is a datetime field..thanks
ASKER
SELECT CONVERT(VARCHAR(7), '2/1/2009', 103)
-->
2/1/200
thx
-->
2/1/200
thx
Please try like the given query.
In your last post, you are applying CONVERT funciton on a varchar type value. first convert it to datetime type and then apply the CONVERT function.
See the below examples for better understanding.
SELECT CONVERT(VARCHAR(10), convert(datetime,'2/1/2009'), 103)
-- 01/02/2009 DD/MM/YYYY format
SELECT RIGHT(CONVERT(VARCHAR(10), convert(datetime,'2/1/2009'), 103),7)
-- 02/2009 MM/YYYY
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>it is a datetime field..thanks
then, just apply my query as posted in the first comment.
to try out with a single value, try out the query given by Sharath in html://#a24257088
then, just apply my query as posted in the first comment.
to try out with a single value, try out the query given by Sharath in html://#a24257088
ASKER
tthank you
ASKER
it gives me 2/1/200
thanks