function to return CW from date

Question

function to return CW from date

radekhatle

SSC Eights!

Points: 955
More actions
August 17, 2010 at 1:08 pm

#236823

Hi there,
I have two function to return CW from date. If I run query from MAnagement studio it return correct date.
But if I run query in Access 2003 which is thin client of DB it return wrong number of CW.
sintax of using function is like select date, fn_isoweek(date) as numberWeek from table.
DB server is SQL2005 standard and 2008 express.
there is source code of this function:
set
ANSI_NULLS ON
set
QUOTED_IDENTIFIER ON
go
FUNCTION [dbo].[fn_ISOweek]
(@AnyDate Datetime )
RETURNS
varchar(2)
AS
BEGIN
Declare
@ThisYear Int
,@PreviousYearStart Datetime
,@ThisYearStart Datetime
,@YearNum Int
,@ISOWeekNum varchar(2)
,@pomocna Varchar(2)
Set @ThisYear = Year(@AnyDate)
Set @ThisYearStart = dbo.udf_DT_YearStart(@ThisYear)
Set @PreviousYearStart = dbo.udf_DT_YearStart(@ThisYear - 1)
If @AnyDate < @ThisYearStart begin
set @ISOWeekNum = Datediff(d, @PreviousYearStart,@AnyDate)/7 + 1
set @YearNum = Year(@AnyDate) - 1
end
Else
begin
set @ISOWeekNum = Datediff(d,@ThisYearStart,@AnyDate)/7 + 1
set @YearNum = Year(@AnyDate)
End
IF
(@ISOWeekNum <10)
begin
SET @pomocna='0'+@ISOWeekNum
end
IF(@ISOWeekNum >=10)
begin
SET@pomocna=@ISOWeekNum
end
RETURN(@pomocna)
End
set
ANSI_NULLS ON
set
QUOTED_IDENTIFIER ON
go
ALTER Function [dbo].[udf_DT_YearStart](@Year As Integer)
RETURNS DATETIME
AS
BEGIN
Declare
@WeekDay As Integer
,@NewYear As DateTIME
,@YearStart as datetime
set @NewYear = Cast((Cast(@Year as varchar(4))+'-01-01') as datetime)
set @WeekDay = (datepart(dw, dateadd(d, -2 , @NewYear)))%7
If
@WeekDay < 4
set @YearStart = dateadd(d, - @WeekDay, @NewYear)
Else
set @YearStart = dateadd(d, 7- @WeekDay , @NewYear)
return
@YearStart
End
Many thanks for replyes.
Radek

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

The difference will be in the connection setting which is most likely affecting the dateformat or the first day of the week.

Check dateformat and first day of week in both environement and then make adjustmenents in your code.

Keep in mind that changing that setting for the whole app is likely to create bugs with dates.