how to get month + year from a date which is of type varchar(20)?

navedjobs · #1 (**permalink**) 12-02-11, 05:45

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

Pat Phelan · #2 (**permalink**) 12-02-11, 09:31

It will perform poorly, but what you want is a LIKE expression.

-PatP

Pat Phelan · #3 (**permalink**) 12-02-11, 09:32

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

r937 · #4 (**permalink**) 12-02-11, 09:51

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

navedjobs · #5 (**permalink**) 12-02-11, 10:03

Quote:

Originally Posted by Pat Phelan

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 .

r937 · #6 (**permalink**) 12-02-11, 10:11

Quote:

Originally Posted by navedjobs

seems perfert but return an empty resultSet in java jdbc .

what expression did you use?

navedjobs · #7 (**permalink**) 12-02-11, 10:29

Quote:

Originally Posted by r937

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 ?";

r937 · #8 (**permalink**) 12-02-11, 10:55

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

navedjobs · #9 (**permalink**) 12-02-11, 11:49

Quote:

Originally Posted by r937

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

Internet Services

Web Development

Digital Marketing

Consumer Tech