Results 1 to 6 of 6

Thread: mySQL - querying datetime field based on year

  1. #1
    Hardcore Geek TheMenace's Avatar
    Join Date
    Nov 2006
    Posts
    791
    Post Thanks / Like

    Default mySQL - querying datetime field based on year

    I'm wondering if there's a simple way to query a mySQL 4 table in order to get all records whose datetime field (say 'dateposted') is equal to a known year?

    e.g:
    select * from table where year(dateposted)=myYearVariable

  2. #2
    Richard Hearne RedCardinal's Avatar
    Join Date
    Feb 2006
    Location
    Dublin/Phuket
    Posts
    985
    Post Thanks / Like

    Default

    There is indeed.
    Code:
    SELECT * FROM table WHERE DATE_FORMAT(dateposted, '%Y') = myYearVariable
    I think something like that will work, but would need to actually try it to make sure. It assumes that the dateposted field is actually a date type.

    Drop me a mail if ou're stuck
    RedCardinal.ie Search Engine Optimisation & Online Marketing
    I offer Web Strategy Consulting and SEO Services.
    Canape catering in Dublin - Amitan.ie
    On Twitter I'm @RedCardinal.

  3. #3
    Hardcore Geek TheMenace's Avatar
    Join Date
    Nov 2006
    Posts
    791
    Post Thanks / Like

    Default

    Thank... but nope, that doesn't seem to work. It's actually a datetime data type.

  4. #4
    Wannabe Geek daviddoran's Avatar
    Join Date
    Aug 2006
    Location
    Meath, Ireland
    Posts
    342
    Post Thanks / Like

    Default

    I always use INT for date/time fields, much easier to work with and you can do more with them.

  5. #5
    Richard Hearne RedCardinal's Avatar
    Join Date
    Feb 2006
    Location
    Dublin/Phuket
    Posts
    985
    Post Thanks / Like

    Default

    What did it say? It might need something like:
    Code:
    SELECT DATE_FORMAT(dateposted, '%Y') AS year, * FROM table WHERE year=myYearVariable
    I haven't had the *pleasure* of SQL db's for a little while...
    RedCardinal.ie Search Engine Optimisation & Online Marketing
    I offer Web Strategy Consulting and SEO Services.
    Canape catering in Dublin - Amitan.ie
    On Twitter I'm @RedCardinal.

  6. #6
    Hardcore Geek TheMenace's Avatar
    Join Date
    Nov 2006
    Posts
    791
    Post Thanks / Like

    Default

    Actually the first one did work. Um, embarrassingly enough I was missing a space from my SQL string that was screwing things up.

    Thanks Richard! Just splendid!

Visitors found this page by searching for:

mysql where year equals

MySQL DateTime Year

mysql query datetime field

mysql date yearmysql get year from date fielddatetime query mysqlmysql order by yearmysql datetime querymysql year to datemysql query date yearmysql select where year from date fieldmysql query datetimemysql query on datetime fieldmysql where date equals yearmysql year equalHow To Get Year From DateTime Field mysqlmysql datetime equaldatetime in mysql queryselect year(datetime) my sqlretreive datetime from mysql field datetimemysql query by datefetch datetime field in mysqlmysql search datetime columnmysql datetime this yearmysql where year from datetime

Tags for this Thread

Posting Permissions

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