POSTGRESQL: Error: date format not recognized

Question

Have a table "user_order" with columns "user_id", "code" (character varying) and created_date_key (integer). I am trying to write a query which displays all records for code 26 and date greater that '12-5-2013 23:59:59'.

Select *
from user_order
where code like 26 ::text
and to_date(created_date_key ::text, 'YYYY-MM-DD') > to_date ('12-5-2013 23:59:59' ::text, 'YYYY-MM-DD')

ERROR: date format not recognized.

Why are you storing a timestamp as an integer? to_date() converts a string (varchar, text) to a date. You cannot pass it an integer. Think about it: how could an integer even when casted to a string possibly have the format 'YYYY-MM-DD' — user330315
– user330315, Commented May 23, 2014 at 19:17
Assuming your integer is a Unix timestamp or similar (seconds since epoch), you should find what you need here: stackoverflow.com/questions/18747317/… and here: stackoverflow.com/questions/12328817/… — IMSoP
– IMSoP, Commented May 23, 2014 at 20:32

daminufe · Accepted Answer · 2014-05-23 19:17:08Z

1

created_date_key should be timestamp and not integer.

answered May 23, 2014 at 19:17

daminufe

9341 gold badge7 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DUnkn0wn1 Over a year ago

I can't change the table structure. That's the way it was build. Just help me with the select statement.

daminufe Over a year ago

Try to remove the dashes then: Instead of YYYY-MM-DD use that format: YYYYMMDD

DUnkn0wn1 · Accepted Answer · 2014-05-26 16:50:22Z

1

select *
  from user_order
 where code like 26 ::text
   and to_date(created_date_key ::text, 'YYYY-MM-DD') > '12-5-2013 23:59:59'

edited May 26, 2014 at 16:50

answered May 23, 2014 at 19:43

DUnkn0wn1

4111 gold badge10 silver badges23 bronze badges

3 Comments

Kevin Over a year ago

Please give some explanation about your answer rather than just the SQL.

user330315 Over a year ago

to_date(created_date_key ::text, 'YYYY-MM-DD') is not going to work and is exactly what Rohit already tried.

DUnkn0wn1 Over a year ago

Convert the integer date to date (1st to string and then to date) as you cannot convert a number directly to date and then check if the date converted is greater than '12-5-2013 23:59:59'

Joseph B · Accepted Answer · 2014-05-23 20:43:04Z

0

String values need to be included within single quotes. You do not need to specify text to convert the data type. to_timestamp function is used to convert the text data to timestamp value. The following query would select the required data:

Select *
from user_order
where code = '26'
and created_date_key > to_timestamp ('12-5-2013 23:59:59', 'MM-DD-YYYY HH24:MI:SS');

References:

SELECT on PostgreSQL Manual. There are a lot of examples on this page.
Data Type Formatting Functions on PostgreSQL Manual

answered May 23, 2014 at 20:43

Joseph B

5,7291 gold badge17 silver badges19 bronze badges

1 Comment

DUnkn0wn1 Over a year ago

created_date_key is a integer. You are trying to compare integer with timestamp.

Collectives™ on Stack Overflow

POSTGRESQL: Error: date format not recognized

3 Answers 3

2 Comments

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related