Extract day of week from database field in PostgreSQL

Question

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?

Craig Ringer · Accepted Answer · 2014-10-09 15:28:45Z

2

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).

answered Oct 9, 2014 at 15:28

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

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

1 Comment

itsols Over a year ago

Thanks for the answer. I tried using TIMESTAMP to cast it thinking that my field was only a DATE and NOT DATETIME. But then like you said it wasn't necessary. I also found in the docs online that a DATE is automatically cast as TIMESTAMP. So there was no need for casting.

Collectives™ on Stack Overflow

Extract day of week from database field in PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related