brown2005 Posted September 5, 2006 Share Posted September 5, 2006 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 Link to comment https://forums.phpfreaks.com/topic/19752-date-in-sql-statements/ Share on other sites More sharing options...
master82 Posted September 5, 2006 Share Posted September 5, 2006 WHERE year = $yearIs that what you mean? Link to comment https://forums.phpfreaks.com/topic/19752-date-in-sql-statements/#findComment-86254 Share on other sites More sharing options...
brown2005 Posted September 5, 2006 Author Share Posted September 5, 2006 no i want to group by year so it is like2002 - 52004 - 32006 - 3and so onso it counts how many records there are for each year Link to comment https://forums.phpfreaks.com/topic/19752-date-in-sql-statements/#findComment-86255 Share on other sites More sharing options...
HuggieBear Posted September 5, 2006 Share Posted September 5, 2006 What database are you using? MySQL, Oracle, MSSQL?Rich Link to comment https://forums.phpfreaks.com/topic/19752-date-in-sql-statements/#findComment-86258 Share on other sites More sharing options...
brown2005 Posted September 5, 2006 Author Share Posted September 5, 2006 MySQL Link to comment https://forums.phpfreaks.com/topic/19752-date-in-sql-statements/#findComment-86259 Share on other sites More sharing options...
brown2005 Posted September 5, 2006 Author Share Posted September 5, 2006 .. Link to comment https://forums.phpfreaks.com/topic/19752-date-in-sql-statements/#findComment-86272 Share on other sites More sharing options...
HuggieBear Posted September 5, 2006 Share Posted September 5, 2006 OK, it shouldn't be too hard...What datatype is your date column? Date, Timestamp, Datetime?Rich Link to comment https://forums.phpfreaks.com/topic/19752-date-in-sql-statements/#findComment-86275 Share on other sites More sharing options...
brown2005 Posted September 5, 2006 Author Share Posted September 5, 2006 date Link to comment https://forums.phpfreaks.com/topic/19752-date-in-sql-statements/#findComment-86278 Share on other sites More sharing options...
HuggieBear Posted September 5, 2006 Share Posted September 5, 2006 OK, give this a try...[code]SELECT year(date_column), count(*)FROM table_nameGROUP BY year(date_column)[/code]RegardsRich Link to comment https://forums.phpfreaks.com/topic/19752-date-in-sql-statements/#findComment-86285 Share on other sites More sharing options...
brown2005 Posted September 5, 2006 Author Share Posted September 5, 2006 spot on rich.thanks very muchRegardsRich (lol) Link to comment https://forums.phpfreaks.com/topic/19752-date-in-sql-statements/#findComment-86287 Share on other sites More sharing options...
brown2005 Posted September 5, 2006 Author Share Posted September 5, 2006 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 Link to comment https://forums.phpfreaks.com/topic/19752-date-in-sql-statements/#findComment-86289 Share on other sites More sharing options...
HuggieBear Posted September 5, 2006 Share Posted September 5, 2006 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_nameGROUP 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 Link to comment https://forums.phpfreaks.com/topic/19752-date-in-sql-statements/#findComment-86291 Share on other sites More sharing options...
HuggieBear Posted September 5, 2006 Share Posted September 5, 2006 [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 Link to comment https://forums.phpfreaks.com/topic/19752-date-in-sql-statements/#findComment-86292 Share on other sites More sharing options...
brown2005 Posted September 5, 2006 Author Share Posted September 5, 2006 nice one, is there a list of the functions like the php ones on phpfreaks.com Link to comment https://forums.phpfreaks.com/topic/19752-date-in-sql-statements/#findComment-86295 Share on other sites More sharing options...
HuggieBear Posted September 5, 2006 Share Posted September 5, 2006 [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]. Link to comment https://forums.phpfreaks.com/topic/19752-date-in-sql-statements/#findComment-86298 Share on other sites More sharing options...
brown2005 Posted September 5, 2006 Author Share Posted September 5, 2006 ok coolthanks for all ur help Link to comment https://forums.phpfreaks.com/topic/19752-date-in-sql-statements/#findComment-86301 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.