Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2011
    Posts
    47

    Unanswered: how to get month + year from a date?

    hi guyz , i have a requirement
    what i want is ,
    in my table i m using varchar to save a date like this 2-dec-2011
    now i have a requirement where i need to select employee who where present in a month so for that i need a quary so that i can pull out all the employees who are present in dec like
    Code:
    select * from emp_attendence where date = 'dec-2011'
    but this returns nothing do you have any ways to achieve what i want to
    thanks
    Last edited by navedjobs; 12-02-11 at 07:40.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It will perform poorly, but what you want is a LIKE expression.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you store the dates as dates, then you have much better choices available because you can use indexes and you can also use date arithmetic.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    couldn't you use
    Code:
    CONVERT(DATE,REPLACE([date],,'-',' '),106) AS actual_date
    put this into a view and then create an index on that column in the view

    then
    Code:
    WHERE actual_date >= '2011-12-01'
      AND actual_date  < '2012-01-01'
    will return rows for december efficiently
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2011
    Posts
    47
    Quote Originally Posted by Pat Phelan View Post
    It will perform poorly, but what you want is a LIKE expression.

    -PatP
    Code:
    like
    seems perfert but return an empty resultSet in java jdbc .

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by navedjobs View Post
    seems perfert but return an empty resultSet in java jdbc .
    what expression did you use?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2011
    Posts
    47
    Quote Originally Posted by r937 View Post
    what expression did you use?
    its an PreparedStatement i m doing every thing right but , im not getting the right output , is there is any special way to use LIKE in java
    Code:
    String listByEmpIdQuary = "select * from emp_att WHERE Emp_id = ? and Date LIKE ?";

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what value did you give for that second question mark?

    what i'm trying to find out is whether you used the right wildcard characters
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Sep 2011
    Posts
    47
    Quote Originally Posted by r937 View Post
    what value did you give for that second question mark?

    what i'm trying to find out is whether you used the right wildcard characters
    yes , i m using the right one which is '%'
    like %-dec-2011
    in which user will give dec and 2011 and this two will always bind with % implicitily

Posting Permissions

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