Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

function to return CW from date Expand / Collapse
Author
Message
Posted Tuesday, August 17, 2010 1:08 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 12:26 PM
Points: 227, Visits: 238
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
Posted Sunday, August 22, 2010 8:44 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: 2 days ago @ 4:00 AM
Points: 21,397, Visits: 9,612
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 »

Add to briefcase

Permissions Expand / Collapse