SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


function to return CW from date


function to return CW from date

Author
Message
radekhatle
radekhatle
Old Hand
Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)

Group: General Forum Members
Points: 309 Visits: 251
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
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68603 Visits: 9671
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search