Vendor specific function

[Nilam]

Hi Expert,

How to use vendor specific function in query?
I hav string '02 2014' to which to want to convert in 'WW YYYY' format.

Plz help... :)

[BigChris]

Hi, I'm probably being exceedingly dense, but I don't understand what you're trying to do. The data you've got already appears to be formatted as WW YYYY...

[Nilam]

I have String 'week 02 2014' which needs to converted to date foramt(WW YYYY) so as to get start date of particular week.

[BigChris]

What's the date of the first day of the first week in your year? You'll need to extract the week number from your field (in your example 2), subtract 1 from that number then multiply by 7, and add that number of days to the date of the first date of the first week.

I'm making the assumption that you're using a relational data source - if it's dimensional then that's a completely different situation

[Lynn]

You might also want to check with your source about how week numbers are derived.

The ISO standard is based on full weeks -- week 1 is the week in which the year's first Thursday occurs and can span years in cases like this year where week 1 begins on December 30 of 2013.

http://www.epochconverter.com/epoch/weeknumbers.php

If your data source has a date dimension I'd suggest having that augmented to include the week start information. Otherwise you have no choice but to do the date arithmetic as Chris has suggested. It can get messy depending on how "week" is defined in your situation.

[navissar]

I gave a rough answer here, and Nilam had acknowledged that it worked for him. Double thread, but hey, if it works...
http://www.cognoise.com/index.php/topic,24064.0.html

[Lynn]

Seems to be a number of people lately who are posting their same issue more than once. Really annoying when I find my volunteered time has been wasted by answering something that has already been addressed.  :-\

I do think the ISO comment is a worthy addendum though. In the case of 2011, the first week of the year begins on January 2nd because that is the week when the first Thursday of the year occurs. Using January 1 as the peg for finding the first week is not correct if the data source is following the ISO standard. Everything would be off by a week in that case, so it is important to understand the definition of "week" when doing these computations. Might be fine using the 1st, but something no one but the OP can determine since it depends on his/her source that we aren't privy to.

Nilam, it would be nice if you review the Forum Etiquette sticky (http://www.cognoise.com/index.php/topic,24030.0.html) and update the subjects of ALL the threads for the same topic to indicate that they are resolved. Better yet, stick with one thread in the future and then just mark that one resolved  :)

Fearless Muppet Moderator, it doesn't look like there is anything specific about not double posting in the etiquette sticky (although the search item # 3 alludes to it), but might be something worth adding? Maybe it is just my pet peeve and others are fine with it  :P

[MFGF]

Fearless Muppet Moderator, it doesn't look like there is anything specific about not double posting in the etiquette sticky (although the search item # 3 alludes to it), but might be something worth adding? Maybe it is just my pet peeve and others are fine with it  :P

<rubs chin thoughtfully, pretending to appear wise and considerate>

Hmmm. You're right. You be askin', me be doin'! :) By the (allegedly) magic keys of this here Lenovo thingummy, I managed to rustle up a new Point 4...

We can't be having pet peeves, 'cos that sounds like fun and we don't do fun. Nope. Serious lot, us Cognoisers! :)

[navissar]

I do think the ISO comment is a worthy addendum though. In the case of 2011, the first week of the year begins on January 2nd because that is the week when the first Thursday of the year occurs. Using January 1 as the peg for finding the first week is not correct if the data source is following the ISO standard. Everything would be off by a week in that case, so it is important to understand the definition of "week" when doing these computations. Might be fine using the 1st, but something no one but the OP can determine since it depends on his/her source that we aren't privy to.

Your comment is pure gold and should be stapled on the walls of the office of every developer dealing with weeks. I tend to assume that when people ask questions here, they know their own requirements, and thus can make the necessary adjustments and corrections for such matters which are a matter of arbitrary choice (It isn't more "Correct" to only count the first week if Jan 1st is a Thursday or earlier than it is to, for instance, count the first week of the year as beginning on the first Monday in January...).
For example: when it comes to weeks, my neck-of-the-woods has a unique feature - weeks in Israel begin on Sunday and end on Saturday. So, when you people kick back on a Sunday, I'm actually in the office; when you say TGIF I'm already on my weekend. This makes for a lot of awkwardness. First, there's the whole issue of songs such as  "I don't like Mondays" and "Manic Monday" which make very little sense here. Then there's the issue of first day of the week and the necessary adjustments to ISO and/or other standards. And then you have all sorts of international companies, where the Israeli consumers would like their week to be Sun-Sat; whereas the rest of the people in the world would like to see their week properly. Just the calculations for _day_of_week functions can leave the chords in my brain tangled for days.
So, I wrote my solution expecting that any requirement as to how the first week of a year is defined that is known to Nilam but not to me will be adjusted for, but now I think that my answer won't be complete without yours, Lynn.

So, Oi! Muppet! Can we maybe join Lynn's answer to the thread where I answered or vice versa for the benefit of PotF (People of the Future)?  :)

[BigChris]

I have to admit that I kept my reply reasonably woolly precisely because the OP needed to know the requirements before the question could be properly answered. By giving a flavour of the answer I hoped it would help them to reconsider the question...