SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Getting month from date

    I have a blog that I'm trying to customize, and I'm trying to extract the month from each entry date. The entry date format looks like this:

    1282078080

    Which (using the php date function) translates to Tuesday, August 17, 2010.

    But I need the month at the query level, because I want to group older posts by month. I've tried doing this:

    Code MySQL:
    SELECT  blog_id
    			, category_id
    			, entry_author_id
    			, entry_id
    			, entry_name
    			, entry_date
    			, month(entry_date) as postmonth
    		FROM blog_entries
    		WHERE entry_author_id = 1
    		AND entry_status = 'published'
    		ORDER BY entry_date DESC
    		LIMIT 5, 15
    		");

    But I get a blank. Not sure why php can pull a date from that string but the month() function can't. I also tried monthname() with the same (lack of) results.

    What am I doing wrong?
    <cfset myblog = "http://cydewaze.org/">

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cydewaze View Post
    What am I doing wrong?
    you're using a date function on an integer -- you have to convert the integer to a date first, and then there are 2 ways of extracting the month

    MONTH(FROM_UNIXTIME(entry_date))

    or

    FROM_UNIXTIME(entry_date,'%c')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    That did it! I ended up using the second option, with the &#37;M flag.

    Thanks!
    <cfset myblog = "http://cydewaze.org/">


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •