Hi All
We have a problem with DATEFIRST.
Different users connecting to SQL 2005 and querying the value for DATEFIRST (SELECT @@DATEFIRST). Some users get 7 (Sun), others get 1 (Mon).
What is giving rise to the different values ?
(All users seem to have the same language settings in SQL and their regional settings are the same).
This is impacting stored procs calling DATEPART that are run from within VB6 Apps using ADO.
Thanks in advance
Preet
I have created a proc that uses @@datefirst to have the weekending on Friday and starting on Saturday.
If I execute the proc from w/in a cursor, a nested cursor, will I have to constantly check and confirm the @@datefirst value?
TIA
JeffP....
Hi
is there any option for changing the first day of week on all tables in msdb tables
i don't want to use set datefirst everytime because i live in europe
Hi All,
My week starts on Monday rather than on Sunday which is default(US, English) in SQL Server and would like to change the same so I would get proper weeknumber and dayindex using DATEPART.
I am looking at different ways of setting DATEFIRST(SET DATEFIRST 1) in SQL Server 2005.
I could set in a stored procedure, but this isn't a feasible way for me because I am using .nettiers to generate by business objects and stored procedures. I need to alter the sp's everytime I newly generate the code and sp's.
I couldn't set it in a function which I was hoping initially. I understand this datefirst is stored in one of the sys table in MASTER DB and I couldn't find a straight forward way to change this.
Can anyone suggest me a way to set the DATEFIRST either at a database level or at a server level(probably by changing the sys table in MASTER DB).
Any help on this would be greatly appreciated.
Ponnu
Trellisys.net
So I need to select and average a whole bunch of data by week. Currently, I group by:GROUP BY TO_CHAR(m.ENDTIME, 'IW')
and select using
SELECT ... TO_CHAR(m.ENDTIME, 'IW') Week
to summarize by week.
Since the first day of the year was a monday, it sets the weeks as monday-monday. 2008 will start the week on Tuesday. I need to set the weeks to be Thursday 7pm to Thursday 7pm. How can this be achieved? I'm not sure if datefirst is the answer. Can it accept decimals? Is there another way to do this?
I'm placing this query into a SqlDataSource selectcommand. Unfortunately I do not have the option of building a stored procedure. Thanks.
Hi,
I am setting my Dataset in SSRS to define the First Day of the week to Sunday.
So In the Dataset I put this scripting :
==================================
SET DATEFIRST 7;
SELECT city, country, datepart(wk,transactdate) as WeekNo from CatalogTable
==================================
The select statement above working very fine in the report.
But then, when I put in the Parameter which I have set in Report Parameter for a field called Service then I will get the problem.
For example the scripting below : ==================================
SET DATEFIRST 7;
SELECT city, country, datepart(wk,transactdate) as WeekNo from CatalogTable
where Service IN (@PService)
==================================
I have defined @PService in Report Parameter, and in the Parameter Tab in Dataset inside SSRS.
But it kept prompting me with the eror message :
Error Source : .Net SqlClient Data Provider
Error Message : Must declare the scalar variable "@PService".
When I remove the first line SET DATEFIRST 7; then my query working fine.
It seems SSRS cannot accept SET statement, whenever we insert the parameter in there.
Is there any workaround on this ?
Appreciate a lot for your help.
cheers,
Tanipar