In the warehouse , they scan the products to know the details of it. One such is Lot number. Here for a product , this lot number is 4 digit and comes in this format 'YYWW' eg: 0914 , 1003, 0842 . In this the first 2 digit is the year and last 2 digit represents the number of the week of that particular year. So i was asked to retrieve the date i.e the last day of that particular week is going to be the manufacturing date and 2 yrs from that date will be expiration date. When the lot number is scanned it triggers to run this query and returns both manufacturing date and expiration date. The point is i have the block (shown below with an example '0942'and this number is not constant) and i need to create a function for this block to make it a proper query so that when the trigger is called it will return the manufacturing and expiry date. Plz guide me.
Let's think about the year first:
Now, we just add on the number of weeks...
So 0901 would give us Jan 1, 2009. 1002 would give us Jan 8, 2010. Different days of the week. You need to make an adjustment, and this is most easily done using datediff and dateadd.
Take a known end-of-week, and make it a constant marker point. Let's suppose you consider Dec 26, 1999 as that date. Count the days from there, divide by seven (rounding down), and add that many weeks back on.
Now we put that all together, and you get:
That's your function. Use an inline TVF for performance reasons:
And call it using:
answered Jan 21 '10 at 11:54 PM
This should do the trick:
You did all the hard work.
answered Feb 05 '10 at 11:02 PM