PostgreSql + Date Format Convert YYYY-MM-DD to Day, Date Month Year

postgresql sql date-comparison
Rubyist picture Rubyist · Nov 26, 2010 · Viewed 62.5k times · Source

Suppose I am storing this format in my postgre database table. Now I have to compare this date with one of the texbox value contain date in different format. 

Database Date Format :: YYYY-MM-DD For example :: 2010-11-26

Text-Field Date Format :: Day, Month Date, Year :: Fri, Nov 26, 10

Now I have tried to compare this value but no response Plz suggest.

    Select * from table_name where user_id=('11') 
and to_char(effective_date, $$$$$$$$)=trim('Fri, Nov 26, 10');

Now Plz suggest which format should I use in place of $$$$$$$$ ???

Thanks !!!

Answer

Frank Heikens picture Frank Heikens · Nov 26, 2010 
SELECT 
  * 
FROM 
  table_name 
WHERE 
  user_id=('11') 
AND 
  to_char(effective_date, 'Dy, Mon DD, YY') = trim('Fri, Nov 26, 10');

Related questions

Getting date list in a range in PostgreSQL

I'd like to get the list of days between the two dates (including them) in a PostgreSQL database. For example, if I had: start date: 29 june 2012 end date: 3 july 2012 then the result should be: 29 june 2012 30 june 2012 1 july 2012 2 july 2012 3 july 2012 What …

Read More
Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How do I (or can I) SELECT DISTINCT on multiple columns?

I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales …

Read More