Jump to content


Photo

date() in sql statements


  • Please log in to reply
15 replies to this topic

#1 brown2005

brown2005
  • Members
  • PipPipPip
  • Advanced Member
  • 943 posts

Posted 05 September 2006 - 10:55 AM

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

#2 master82

master82
  • Members
  • PipPipPip
  • Advanced Member
  • 182 posts

Posted 05 September 2006 - 11:05 AM

WHERE year = $year

Is that what you mean?

#3 brown2005

brown2005
  • Members
  • PipPipPip
  • Advanced Member
  • 943 posts

Posted 05 September 2006 - 11:10 AM

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

#4 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 05 September 2006 - 11:16 AM

What database are you using?  MySQL, Oracle, MSSQL?

Rich
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#5 brown2005

brown2005
  • Members
  • PipPipPip
  • Advanced Member
  • 943 posts

Posted 05 September 2006 - 11:17 AM

MySQL

#6 brown2005

brown2005
  • Members
  • PipPipPip
  • Advanced Member
  • 943 posts

Posted 05 September 2006 - 11:53 AM

..

#7 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 05 September 2006 - 11:58 AM

OK, it shouldn't be too hard...

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

Rich
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#8 brown2005

brown2005
  • Members
  • PipPipPip
  • Advanced Member
  • 943 posts

Posted 05 September 2006 - 12:01 PM

date

#9 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 05 September 2006 - 12:09 PM

OK, give this a try...

SELECT year(date_column), count(*)
FROM table_name
GROUP BY year(date_column)

Regards
Rich
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#10 brown2005

brown2005
  • Members
  • PipPipPip
  • Advanced Member
  • 943 posts

Posted 05 September 2006 - 12:11 PM

spot on rich.

thanks very much

Regards

Rich (lol)

#11 brown2005

brown2005
  • Members
  • PipPipPip
  • Advanced Member
  • 943 posts

Posted 05 September 2006 - 12:14 PM

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

#12 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 05 September 2006 - 12:15 PM

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

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

... 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
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#13 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 05 September 2006 - 12:16 PM

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


Try monthname() instead of month()

Rich
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#14 brown2005

brown2005
  • Members
  • PipPipPip
  • Advanced Member
  • 943 posts

Posted 05 September 2006 - 12:24 PM

nice one, is there a list of the functions like the php ones on phpfreaks.com

#15 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 05 September 2006 - 12:28 PM

Is there a list of the functions like the php ones on phpfreaks.com


I just went to the MySQL site.
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#16 brown2005

brown2005
  • Members
  • PipPipPip
  • Advanced Member
  • 943 posts

Posted 05 September 2006 - 12:29 PM

ok cool

thanks for all ur help




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users