SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Guru johnjohn2's Avatar
    Join Date
    Apr 2004
    Location
    here
    Posts
    746
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how to compare to fields with date

    I have one date field in the format "1/31/2005" that is actually a url parameter value and a field in a table that keeps track of year only in the format "2005". I'm using postgresql 7.4 in case that makes a difference. Is there a way in the sql to compare these? Will the database know that I'm comparing two dates? I wanna return only records in tbl2 that have similar years:

    tbl2
    record1 2002
    record2 2003
    record3 2004
    record4 2005

    Would return : 2005

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    first of all, if it's a DATE datatype column, it probably isn't stored in that format (i don't know this for certain, but if postgresql is like other databases, the date values are stored internally as whole integers)

    as for comparing a stored date to a year number, use

    ... where extract(year from datefield) = storedyear
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •