How to use CAST to replace ::date in postgreSQL?

Question

How to to replace ::date and use CAST for below SQL code ?

SELECT to_char(d,   'DD Mon YYYY" to "') || to_char(d+6, 'DD Mon YYYY') AS week 
FROM  (
  SELECT generate_series(d1,d1 + interval '4 weeks',interval '1 week')::date AS d
  FROM  (SELECT date_trunc('week', to_date('January2014', 'MonthYYYY')) AS d1) sub1
  ) sub2

Tomas Greif · Accepted Answer · 2014-02-24 08:52:52Z

1

Casting a value to a date can be done in either of the following ways:

value::date
CAST(value as date)

If you would like to create your own conversion function you can use the CREATE CAST command.

CREATE CAST documentation

edited Feb 24, 2014 at 8:52

Tomas Greif

22.9k25 gold badges114 silver badges162 bronze badges

answered Feb 24, 2014 at 6:53

Dwayne Towell

8,6635 gold badges39 silver badges54 bronze badges

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

5 Comments

Amit Sharad Over a year ago

(CAST(d1,d1 + interval '4 weeks',interval '1 week' AS DATE)) gives error like ERROR: syntax error at or near "," in d1,d

Dwayne Towell Over a year ago

That is because you did not put the cast around a value, but around 3 values: d1, d1 + interval '4 weeks', and interval '1 week'.

Amit Sharad Over a year ago

Thanks Dwayne , could you please show how this cane be done ?

Dwayne Towell Over a year ago

Try a simpler query and work up to the one you want. Do you know what generate_series(...) does?

Amit Sharad Over a year ago

got it working as below (CAST (generate_series(d1,d1 + interval '4 weeks',interval '1 week') AS DATE ))

user3283976 · Accepted Answer · 2014-02-24 06:54:50Z

1

Try below commend:

cast(d as date) instead of ::date

For More Reference:http://www.postgresql.org/message-id/[email protected]

answered Feb 24, 2014 at 6:54

user3283976

1774 bronze badges

1 Comment

Amit Sharad Over a year ago

(CAST(d1,d1 + interval '4 weeks',interval '1 week' AS DATE)) gives error like ERROR: syntax error at or near "," in d1,d

Collectives™ on Stack Overflow

How to use CAST to replace ::date in postgreSQL?

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related