First day of week gives different results in sql and crystal reports

Hi rrjegan17

The function is written in sql and then a view is created in sql which is then linked to by crystal reports.
I know it is something to do with how each system interprets the first day of the week, sql give 7 for select @@datefirst but I don't know how to check or change it in crystal.
What I don't understand is that if the function is in sql and the view in sql and gives the correct result then why does crystal not just see the results. It seems like crystal runs the view and funtions from within itself and gets a different value for the datepart(dw,@pInputDate)  and causes my problem.

Ok.. Include this at the beginning of your Function

SET DATEFIRST 7;

Kindly try it out and update me..

I just looked into this one..
It will bring wrong results by fetching last day of earlier week
set @result = convert(datetime,DATEADD(DD,  0-DATEPART(DW, @pInputDate),@pInputDate),126)

Instead you have to use the one below to fetch first day of this week

set @result = convert(datetime,DATEADD(DD,  0-DATEPART(DW, @pInputDate),@pInputDate),126)

Typo..


Instead you have to use the one below to fetch first day of this week

set @result = convert(datetime,DATEADD(DD, 1-DATEPART(DW, @pInputDate),@pInputDate),126)

I should have explained the first day of our week is a Sat and hence the 0-Datepart etc. Either way with the change you sugested the answers are different in SQL and Crystal as above example

In SQL a date of 27/07/2009 give result of 26/07/2009 now a sunday
In Crystal a date of 27/07/2009 gives result of 27/07/2009 which is a monday and wrong

I have tried to use SET DATEFIRST 7 at the begining of funcion bt get error you can't use it in a function

Thanks

Sully

>> I should have explained the first day of our week is a Sat

Have you made any configuration changes for this in SQL Server say.. Regional settings, Date & Time, etc..

no the only differnece is that sql is on our server and crystal on my local machine, maybe there is a difference in reginal settings, I will check and report back.

Sully

O.k the regional settings are the same but I have found a way around the problem, I just need to know how to adjust the first day of week setting in Crystal report.
Currently
SQL is Sun =1 to Sat =7
crystal mon=1 to Sun=7

The code I used is a follows (please remember my 1st day of week needed to be sat)
( @pInputDate    DATETIME )
RETURNS datetime
Begin
Declare @result datetime

if datepart(dw,convert(datetime,@pInputDate,126))= 6 --checks for Sat
      set @result = convert(datetime,@pInputDate,126)
else if datepart(dw,convert(datetime,@pInputDate,126))= 7 --if sunday we want to -1 not -8 as next bit of code would have done
      set @result = convert(datetime,DATEADD(DD,  -1,@pInputDate),126) --deals with all other days
else
      set @result = convert(datetime,DATEADD(DD,  -1-DATEPART(DW, @pInputDate),@pInputDate),126)
 RETURN @result

END

Are you using the same database user in your connection to SQL server from crystal as you are when checking the results in sql server. You may find that datefirst is a user dependant value.

Can you try the slightly modified one, which would work for both SQL Server and Crystal

IF datename(dw,@pInputDate)= 'Saturday'
      set @result = convert(datetime,@pInputDate,126)
ELSE
BEGIN
      set @result = CASE datename(dw,convert(datetime,DATEADD(DD,  0-DATEPART(DW, @pInputDate),@pInputDate),126)) WHEN 'Saturday'
      THEN convert(datetime,DATEADD(DD,  0-DATEPART(DW, @pInputDate),@pInputDate),126)
      ELSE dateadd(dd, -1, convert(datetime,DATEADD(DD,  0-DATEPART(DW, @pInputDate),@pInputDate),126)) end
END

                                              

1:
2:
3:
4:
5:
6:
7:
8:

PCIIain yes I am using the same user when connecting to SQL and Crystal, the only thing I can think of is because the machines are different.
rrjegan17 I copied and pasted your code and get Incorrect syntax near end.

Can you provide the code which you tried so that I can fix in it..

here you are

CREATE FUNCTION [dbo].[fnDayOfWeek3
]
( @pInputDate    DATETIME )
RETURNS datetime
Begin
Declare @result datetime
IF datename(dw,@pInputDate)= 'Saturday'
      set @result = convert(datetime,@pInputDate,126)
ELSE
BEGIN
      set @result = CASE datename(dw,convert(datetime,DATEADD(DD,  0-DATEPART(DW, @pInputDate),@pInputDate),126)) WHEN 'Saturday'
      THEN convert(datetime,DATEADD(DD,  0-DATEPART(DW, @pInputDate),@pInputDate),126)
      ELSE dateadd(dd, -1, convert(datetime,DATEADD(DD,  0-DATEPART(DW, @pInputDate),@pInputDate),126)) 
	
end
 
END
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:

You missed one more END..
This should work..

CREATE FUNCTION [dbo].[fnDayOfWeek3]
( @pInputDate    DATETIME )
RETURNS datetime
Begin
Declare @result datetime
IF datename(dw,@pInputDate)= 'Saturday'
      set @result = convert(datetime,@pInputDate,126)
ELSE
BEGIN
      set @result = CASE datename(dw,convert(datetime,DATEADD(DD,  0-DATEPART(DW, @pInputDate),@pInputDate),126)) WHEN 'Saturday'
      THEN convert(datetime,DATEADD(DD,  0-DATEPART(DW, @pInputDate),@pInputDate),126)
      ELSE dateadd(dd, -1, convert(datetime,DATEADD(DD,  0-DATEPART(DW, @pInputDate),@pInputDate),126)) end
END 
END

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:

Hi

I tried the code and it gives different answers to what I have. I am comparing against known answers so I would say that the code you provided may not work in crystal as you expect. I am thinking there is maybe a config setting I need to change to get crystal to see sun=1 to Sat=7 so matches SQL

Thanks

Sully

I thought the default in Crystal was Sun=1 Sat = 7

Crystal date functions allow the use of a FIrstDayofWeek indicator.

mlmcc

sullym6,
    Have one question here..

You are creating an SQL Function.
So whatever date you provide from Crystal or SQL Server to that Function, it should provide the same set of output since it is going to execute in SQL Server only and not in any other place.

Kindly confirm

rrjegan17, it is very strange what is happening and although I have written a function which works it is still not ideal.
The function I posted is the one I am using in a query in SQL this is then linked to via odbc connection by crystal. Now running the query in SQL and browsing the field data in crystal give differen results. We have worked out that Crystal in looking at the weekday numbers differently  but I have no idea of how to change this.
I hope that answers your question

I have included my function code and also the query written in sql. As you can see from the screenshots the same query gives different results as a result of different answers from datepart.
Sully

CREATE FUNCTION [dbo].[fnDayOfWeek2]
( @pInputDate    DATETIME )
RETURNS datetime
Begin
Declare @result datetime
 
if datepart(dw,convert(datetime,@pInputDate,126))= 6
	set @result = convert(datetime,@pInputDate,126)
else if datepart(dw,convert(datetime,@pInputDate,126))= 7
	set @result = convert(datetime,DATEADD(DD,  -1,@pInputDate),126)
else
	set @result = convert(datetime,DATEADD(DD,  -1-DATEPART(DW, @pInputDate),@pInputDate),126)
 RETURN @result
 
END
 
----query using above
 
CREATE VIEW dbo.vqryWeeklyTotals
AS
SELECT     dbo.fnDayOfWeek2(BookingDate) AS Weeks, SUM(TimeInHours) AS TimeA, SUM(TimeInHoursRateA) AS TimeB, SUM(TimeInHoursRateB) 
                      AS TimeC
FROM         dbo.tblTimesheetBookings
WHERE     (BookingDate >= CONVERT(DATETIME, '2009-07-01 00:00:00', 102))
GROUP BY dbo.fnDayOfWeek2(BookingDate)

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:

Your datefirst setting in SQL server has got to be different for the two queries.

1. Try running a command in a crystal report :-
select @@datefirst as date_first, dbo.fnDayOfWeek2(current_timestamp) AS Testdate
and see what this returns.

2. Try explicitly setting datefirst in your function

There is a difference as I have proved this already with my results
The result of your query in SQL
Datefirst = 7
Testdate = 11/09/2009
and in crystal
Datefirst =1
Testdate=12/09/2009

I have tried to set datefirst my function but get error this is not allowed.

Thanks

Sully