Extract Week number and year from Date

Dev Shed Forums Sponsor:

July 11th, 2012, 01:17 AM

karthikprs

Registered User

Join Date: Jul 2012

Posts: 4

Time spent in forums: 1 h 2 m 30 sec
Reputation Power: 0

Extract Week number and year from Date

I tried extracting week number and year from an existing date.
The output i get using "extract" method from datepart is:
Weeknumber year date
1 2012 2012-01-01

But the output i require is:
Week date
01/2012 2012-01-01
Please help me on getting the output in this format....

July 11th, 2012, 01:31 AM

shammat

Contributing User

Join Date: Oct 2003

Location: Germany

Posts: 2,701

Time spent in forums: 1 Month 3 Weeks 4 Days 2 h 17 m 41 sec
Reputation Power: 284

Use to_char()

Code:

select to_char(your_date_column, 'IW/YYYY YYYY-MM-DD')

More details in the manual: http://www.postgresql.org/docs/current/static/functions-formatting.html

__________________
I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
http://forums.devshed.com/misc.php?do=bbcode#code

Tips on how to ask better questions:
http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
http://wiki.postgresql.org/wiki/SlowQueryQuestions
http://catb.org/esr/faqs/smart-questions.html

July 11th, 2012, 03:18 AM

karthikprs

Registered User

Join Date: Jul 2012

Posts: 4

Time spent in forums: 1 h 2 m 30 sec
Reputation Power: 0

Thanks

Hi Shammat,
thanks for the query. but the output i want for week is: "week01/2012" i need the prefix week before week number.

Quote:

Originally Posted by shammat

Use to_char()

Code:

select to_char(your_date_column, 'IW/YYYY YYYY-MM-DD')

More details in the manual: http://www.postgresql.org/docs/current/static/functions-formatting.html

July 11th, 2012, 04:42 AM

shammat

Contributing User

Join Date: Oct 2003

Location: Germany

Posts: 2,701

Time spent in forums: 1 Month 3 Weeks 4 Days 2 h 17 m 41 sec
Reputation Power: 284

Quote:

Originally Posted by karthikprs

but the output i want for week is: "week01/2012" i need the prefix week before week number.

Then append the string "week" to the expression. Check the manual for "string concatenation"

http://www.postgresql.org/docs/current/static/functions-string.html

July 11th, 2012, 09:02 AM

karthikprs

Registered User

Join Date: Jul 2012

Posts: 4

Time spent in forums: 1 h 2 m 30 sec
Reputation Power: 0

Thank u Shammat

Thank u Shammat. It was very useful

Extract Week number and year from Date

Developer Shed Advertisers and Affiliates