# Need function to convert from yyyymm to smalldatetime

hi
I have a Calendar table that lists yyyyww (IntelYearWW) values (eg; 200847) and dates for those weeks (IntelDate) (eg: for 200847 the dates would range from the Sunday 16 Nov to the Saturday 22/Nov)
I need a function that will  take yyyyww as an input value, and output the first date (min(IntelDate) for that week
thanks
Fergal
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
try this
``````declare @currentweek int
set @currentweek = 200847
declare @firstDayYear as datetime
set @firstDayYear = CAST('01/01/' + LEFT(@currentweek,4) AS DATETIME)
set @currentweek = RIGHT(@currentweek,2)
``````
0
Author Commented:
Hi
thanks but I need a function that directly references my Calendar table
0
Commented:

``````CREATE FUNCTION GetMinWeekDate
(  @currentweek int)
RETURNS DATETIME
AS
BEGIN

declare @firstDayYear as datetime
set @firstDayYear = CAST('01/01/' + LEFT(@currentweek,4) AS DATETIME)
set @currentweek = RIGHT(@currentweek,2)

declare @FirstWeekDate as datetime

declare @LastWeekDate as datetime

return (select MIN(IntelDate) from TableName where IntelDate between @FirstWeekDate and @LastWeekDate)

END
``````
0
Author Commented:
thanks but I got it figured out:

ALTER FUNCTION dbo.fn_yyyyww_to_date
(
-- Add the input parameters for the function here
@IntelWW as int
)
RETURNS TABLE
AS
RETURN
(
select distinct IntelYearWW, min(IntelDate) as first_day_IntelWW
from dbo.Calendar where IntelYearWW = @IntelWW group by IntelYearWW
)
0
Author Commented:
derived solution from another function
0
Author Commented:
derived solution from another function
0

Experts Exchange Solution brought to you by