function to return CW from date

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » function to return CW from date

function to return CW from date

Rate Topic

Display Mode

Topic Options

Author

Message

radek-902543

Posted Tuesday, August 17, 2010 1:08 PM

SSC Veteran

Group: General Forum Members
Last Login: Sunday, October 02, 2011 4:49 AM
Points: 225, Visits: 235

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

Post #970671

Ninja's_RGR'us

Posted Sunday, August 22, 2010 8:44 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 4:09 AM
Points: 21,357, Visits: 9,539

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.

Post #973060

« Prev Topic | Next Topic »

Permissions