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 Quote 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? Quote 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 Quote 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 Quote 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 Quote 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 .. Quote 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 Quote 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 Quote 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 Quote 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) Quote 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 Quote 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 Quote 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 Quote 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 Quote 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]. Quote 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 Quote Link to comment https://forums.phpfreaks.com/topic/19752-date-in-sql-statements/#findComment-86301 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.