CODEWITHSUNDEEP
datetimepostgresql-9.1
itsols
itsols

Reputation: 5582

Extract day of week from database field in PostgreSQL

I simply want to show a date and its day of the week from a table.

The following works:

select "invDate", (select extract (dow from timestamp '2014-09-22'))
from "tblInvMaster"

But the moment I try to use the actual field like the example below, it doesn't work:

select "invDate", (select extract (dow from timestamp "invDate"))
from "tblInvMaster"

The above gives a syntax error where the field name starts in timestamp.

What is the correct method of getting this to work?

Upvotes: 3

Views: 3347

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324375

The syntax

TYPENAME 'VALUE'

e.g.

TIMESTAMP '2014-01-01'

is only valid in SQL for type literals.

If you want to cast a non-literal value you must use an explicit cast. Most likely you don't require a cast at all, and can just write:

extract(dow from "invDate")

as "invDate" should already be a timestamp or date. If it isn't, you'll need to CAST("invDate" AS timestamp).

Upvotes: 2

Related Questions