SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    I'm not a human
    Join Date
    Aug 2006
    Location
    India
    Posts
    281
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question How to get records between two specific dates?

    hi,

    i'm a newbee to mysql.


    i'm having a table called transaction which stores transaction details with dates.

    i'm having another table callled company in which the company info stored,

    The company table has the fiscal month details.

    what i wnt to do is get the records from the transaction which is between two specific dates(i.e) in the fiscal year

    eg: 04-01-2006 to 03-31-2007

    as far as now i'm having the query,

    Code:
    SELECT t.* FROM transaction t,company c where t.companyid=c.id AND
    (MONTH(t.transdate)>MONTH(str_to_date(c.fiscalmonth,'%M')) AND
     YEAR(t.transdate)=YEAR(now())) ||  
    (MONTH(t.transdate)<MONTH(str_to_date(c.fiscalmonth,'%M')) AND 
    YEAR(t.transdate)=YEAR(now())+1) and c.id=2 order by transdate
    Note: the company table stores the fical months as month names(i.e) March etc.,

    i think this is far complex, and is there any easy function to get the records b/w two dates??


    how can i handle this??
    Regards,
    Vijay
    Follow me on twitter @vijaycbe
    World Holiday Calander || My Cricket Blog

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    The BETWEEN keyword would be optimal if you had actual dates. I suppose you're not able to change the company table to store real dates instead of month names? You'll still have to do some work to cast them to dates, but BETWEEN might make your query easier to read.

  3. #3
    I'm not a human
    Join Date
    Aug 2006
    Location
    India
    Posts
    281
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    The BETWEEN keyword would be optimal if you had actual dates. I suppose you're not able to change the company table to store real dates instead of month names? You'll still have to do some work to cast them to dates, but BETWEEN might make your query easier to read.
    yes you are right,

    i can't change the values in the company table.

    then how can cast the values into a date,

    i've no clue.

    now i've the only month value ,

    the follwing query returns the value '3'

    Code:
    SELECT MONTH(str_to_date(c.fiscalmonth,'%M')) from company c where c.id=2;
    from here how can i get the two dates and what is the syntax for casting..
    Regards,
    Vijay
    Follow me on twitter @vijaycbe
    World Holiday Calander || My Cricket Blog

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    what do you get for this query --
    Code:
    select c.fiscalmonth
      from company as c
     where c.id = 2
    the part i'm having trouble with is this...

    suppose the company's fiscal month is April

    if you run that query today, you will get the range 2006-04-01 to 2007-03-31

    however, if you run that query two weeks from today, you will get 2007-04-01 to 2008-03-31 and that cannot be what you want
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    I'm not a human
    Join Date
    Aug 2006
    Location
    India
    Posts
    281
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    what do you get for this query --
    Code:
    select c.fiscalmonth  from company as c where c.id = 2
    the part i'm having trouble with is this... suppose the company's fiscal month is April
    if you run that query today, you will get the range 2006-04-01 to 2007-03-31

    however, if you run that query two weeks from today, you will get 2007-04-01 to 2008-03-31 and that cannot be what you want
    oops !! i haven't think that way. That could be more dramatic if that query runs.

    the company table has a column ficalmonth and it never change

    so i think the query
    Code:
    select c.fiscalmonth
      from company as c
     where c.id = 2
    wouldn't do much wrong the part which is dreadfully in danger is getting the whole date..

    but as you told if run that query after two weeks it would give wrong entries.

    let me explain the condition well, i just want to fetch the transaction from the transaction table with respect to the companyId and that fall in the accounting year.

    How can i handle this??

    i deadly need that!!!!!
    Regards,
    Vijay
    Follow me on twitter @vijaycbe
    World Holiday Calander || My Cricket Blog

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    so what is the fiscalmonth value for company 2?

    and which fiscal year do you want?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    I'm not a human
    Join Date
    Aug 2006
    Location
    India
    Posts
    281
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi ,

    the fiscal month will be march for the company 2.

    but it is something big such that every time a user logs in , his company id is recollected and then the fiscal month has to be determined from the company table and then the transactions from the transaction table has to be fetched which falls in b/w the accounting year.

    how can i handle this with a common query???


    however, if you run that query two weeks from today, you will get 2007-04-01 to 2008-03-31 and that cannot be what you want
    (with concideration that the time interval should not change at any time of the year.. )
    Regards,
    Vijay
    Follow me on twitter @vijaycbe
    World Holiday Calander || My Cricket Blog

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    do you still require a solution for this?

    sorry, i took a bit of a break

    i think i can do this in a single query, if you still need it, but it would take a bit of work, so i thought i'd ask first
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    I'm not a human
    Join Date
    Aug 2006
    Location
    India
    Posts
    281
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes still really need this ,

    If you help it will be very useful for me..
    Regards,
    Vijay
    Follow me on twitter @vijaycbe
    World Holiday Calander || My Cricket Blog


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
  •