#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    4
    Rep 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....
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,777
    Rep Power
    348
    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
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    4
    Rep 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.
    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
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,777
    Rep Power
    348
    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
    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
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    4
    Rep Power
    0

    Thank u Shammat


    Thank u Shammat. It was very useful

IMN logo majestic logo threadwatch logo seochat tools logo