[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Need function to convert from yyyymm to smalldatetime

Posted on 2008-11-18
Medium Priority
455 Views
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
0
Question by:fjkilken
• 4
• 2

LVL 14

Expert Comment

ID: 22983436
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 Comment

ID: 22983485
Hi
thanks but I need a function that directly references my Calendar table
0

LVL 14

Expert Comment

ID: 22983559

``````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 Comment

ID: 22983582
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 Comment

ID: 23076377
derived solution from another function
0

Accepted Solution

fjkilken earned 0 total points
ID: 23667756
derived solution from another function
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
###### Suggested Courses
Course of the Month19 days, 22 hours left to enroll