3 Replies - 557 Views - Last Post: 04 November 2013 - 06:48 AM Rate Topic: -----

#1 brerallia  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 146
  • Joined: 21-January 13

Error in using LIKE in postgresql

Posted 04 November 2013 - 03:50 AM

I have this code:

select * from tbl_leave_request  where date_requested like '%01-01';


is seriously not working...
i googled the problem and found out another way... it is to try it altering...

i altered it saying "Query successfully ...bla bla with no result affect by... bla bla.."

how to use the date in like???

ANY HELP WILL BE APPRECIATED...

Is This A Good Question/Topic? 0
  • +

Replies To: Error in using LIKE in postgresql

#2 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3220
  • View blog
  • Posts: 10,801
  • Joined: 12-December 12

Re: Error in using LIKE in postgresql

Posted 04 November 2013 - 04:04 AM

like is for text comparisons. If date_requested is, sensibly, a date-field then you should use date functions to return the dates that you need.
Was This Post Helpful? 1
  • +
  • -

#3 brerallia  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 146
  • Joined: 21-January 13

Re: Error in using LIKE in postgresql

Posted 04 November 2013 - 04:14 AM

View Postandrewsw, on 04 November 2013 - 04:04 AM, said:

like is for text comparisons. If date_requested is, sensibly, a date-field then you should use date functions to return the dates that you need.


does it function the way LIKE???
anyway how to alter the datatype..?? i dont know how.. ive already read it and it doesnt work right...
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3220
  • View blog
  • Posts: 10,801
  • Joined: 12-December 12

Re: Error in using LIKE in postgresql

Posted 04 November 2013 - 06:48 AM

LIKE and date-functions do not work in the same way.

I don't use Postgre but most databases won't allow changing the data-type of a field, or only allow it between certain data-types. If your dates are not already a date/time field then you'll need to do the following:

  • Add a new date/time field.
  • Run an UPDATE query that sets this new field's values to a date/time obtained from the current text field.

This UPDATE query will need to convert the text-field values into an acceptable date-format. That is, a string version of the value currently held in the existing field that is in a format that Postgre recognises as valid dates.

Once you've obtained this new column you can delete the old column and rename the new one.

If the new field is named date_requested then:

SELECT EXTRACT(YEAR FROM date_requested) FROM tbl_leave_request;

will return values 2010, 2010, 2012, etc..

To only return, for example, records from 2010:

SELECT * FROM tbl_leave_request WHERE EXTRACT(YEAR FROM date_requested) = 2010;

You can use similar expressions, and date arithmetic, to work with the month, day, etc., from your date-field.

Again, I don't use Postgre so my examples may need slight corrections.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1