Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2007
    Location
    Toronto, Ontario
    Posts
    11

    Unanswered: a query to extract months from the date

    the table = ordertbl and the column name is orddate

    i tried using MID(ordertbl.orddate,0,2) but it does not return the 08, 09, instead it returns 8/, 9/ which messes up the order by

    the date looks like this '01/22/2007' mm/dd/yyyy

    how can i extract the months so it can be 01 to 09 without taking the 0 out

    thanks in advance
    Last edited by mackenzo; 10-09-07 at 00:40.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Start here.

    -PatP

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what is the datatype of the column? DATETIME or VARCHAR?

    and are you sure you're actually using SQL Server?

    because MID is not a valid SQL Server function
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Oct 2007
    Location
    Toronto, Ontario
    Posts
    11
    Quote Originally Posted by r937
    what is the datatype of the column? DATETIME or VARCHAR?

    and are you sure you're actually using SQL Server?

    because MID is not a valid SQL Server function

    Hi again, this is for a school assignment
    it's Date/Time
    im using Access right now because i dont know how convert an access table file to sql server management.
    what i did so far.


    Code:
    select * from(select MID(ordertbl.orddate,3,2) as MonthValue, employee.empno, employee.emplastname, (product.prodprice * sum(ordline.qty)) * employee.empcommrate as TotCommAmt from ordertbl, ordline, product, employee where ordertbl.ordno=ordline.ordno and ordline.prodno=product.prodno and employee.empno=ordertbl.empno group by MID(ordertbl.orddate,3,2), product.prodprice, employee.empno, employee.emplastname, employee.empcommrate) order by totcommamt desc,monthvalue asc
    but the ORDER BY dont work properly because of the month


    The QUESTION:
    List the total commission earned by each employee in each month of 2007. The result should include the month, employee number, employee last name, and the total commission amount earned in that month. The amount of a commission for an
    individual employee is the sum of the dollar amount of products ordered times the
    commission rate of the employee.
    Sort the result by the month in ascending month
    number and the total commission amount in descending order. For SQL Server, the
    month number can be extracted by the Month function with a date as the argument.


    P.S i am not sure if the total commission cal. is correct. but anyway, i just want the month to work correctly for now

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mackenzo
    it's Date/Time
    so it's Date/Time, not text, so don't use a string function on it, use a date function

    replace MID(ordertbl.orddate,3,2) with MONTH(ordertbl.orddate)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Oct 2007
    Location
    Toronto, Ontario
    Posts
    11
    Thanks r937

Posting Permissions

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