PostgreSQL to_date()

Question

I have a problem with PostgreSQL to_date() function, in my code mentioned bellow I get syntax error but can't find where.

SELECT * from crosstab('select upit, sat, count(*) as broj
                                from upiti
                                where datum >= to_date('26-10-2014','DD-MM-YYYY') AND datum <= to_date('29-10-2014','DD-MM-YYYY')
                                group by upit,sat
                                ORDER BY upit,sat',

'select rbrSat from sat order by rbrSat') as ct (upit text, s00 INT, s01 INT, s02 INT, s03 INT, s04 INT, s05 INT, s06 INT, s07 INT, s08 INT, s09 INT, s10 INT, s11 INT, s12 INT, s13 INT, s14 INT, s15 INT, s16 INT, s17 INT, s18 INT, s19 INT, s20 INT, s21 INT, s22 INT, s23 INT);

It says that syntax error is near "2014". Anyone knows where is the problem?

This snippet looks fine. You should edit the question and add in the full query (and, you probably don't need to_date() when using dates in YYYY-MM-DD format). — Gordon Linoff
– Gordon Linoff, Commented Oct 26, 2014 at 15:00
I suspect you have a missing end quote ' earlier in the query, making the previous string end just before 2014 instead of starting just before it. — Joachim Isaksson
– Joachim Isaksson, Commented Oct 26, 2014 at 15:01
yeah i pasted the wrong snippet with that YYYY, but te real problem was in ' that was supposed to be ''. — DynamicsNinja
– DynamicsNinja, Commented Oct 26, 2014 at 19:03

Joachim Isaksson · Accepted Answer · 2014-10-26 15:15:31Z

3

You're trying to use unescaped single quotes ' inside a single quoted string. If you need to add a single quote inside a string, it needs to be doubled, as in '';

SELECT * from crosstab('select upit, sat, count(*) as broj
                        from upiti
                        where datum >= to_date(''26-10-2014'',''DD-MM-YYYY'') 
                          AND datum <= to_date(''29-10-2014'',''DD-MM-YYYY'')
                            group by upit,sat
                            ORDER BY upit,sat',

...

answered Oct 26, 2014 at 15:15

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

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

2 Comments

vyegorov Over a year ago

Better to use dollar quoting here ($$), in fact, quite handy.

user330315 Over a year ago

Using an ANSI date literal: DATE '2014-10-26' makes it even shorter

Collectives™ on Stack Overflow

PostgreSQL to_date()

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related