Jump to content

Archived

This topic is now archived and is closed to further replies.

brown2005

date() in sql statements

Recommended Posts

if i have

$sql = "SELECT * FROM table1 GROUP BY year";

but i only have a date field in my table, so how can i get just the year from it

Share this post


Link to post
Share on other sites
no i want to group by year so it is like

2002 - 5
2004 - 3
2006 - 3

and so on

so it counts how many records there are for each year

Share this post


Link to post
Share on other sites
OK, it shouldn't be too hard...

What datatype is your date column?  Date, Timestamp, Datetime?

Rich

Share this post


Link to post
Share on other sites
OK, give this a try...

[code]
SELECT year(date_column), count(*)
FROM table_name
GROUP BY year(date_column)
[/code]

Regards
Rich

Share this post


Link to post
Share on other sites
one more thing, before i leave u alone, lol, if say i use month(), obviously it prints out 9 as the numeric month, but how can i print out September instead

Share this post


Link to post
Share on other sites
Glad it worked, taught myself something new there too.  I'm used to working in Oracle, so I took the following oracle code...

[code]
SELECT to_char(date_column, 'YYYY') "Date", count(*) "Count"
FROM table_name
GROUP BY to_char(date_column, 'YYYY')
ORDER BY "Date" DESC
[/code]

... and then worked out what the equivalent functions were in MySQL.

I've posted this here for anyone who comes across your post and wonders how to do it in Oracle.

Rich

Share this post


Link to post
Share on other sites
[quote]
one more thing, before i leave u alone, lol, if say i use month(), obviously it prints out 9 as the numeric month, but how can i print out September instead
[/quote]

Try monthname() instead of month()

Rich

Share this post


Link to post
Share on other sites
[quote]
Is there a list of the functions like the php ones on phpfreaks.com
[/quote]

I just went to the [url=http://dev.mysql.com/doc/refman/5.0/en/]MySQL site[/url].

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.