## Week : howto find the date of the first day of a week#

### Week : howto find the date of the first day of a week#

Hi everybody !

Does anybody has to do it ?

I had to know the first of a week number nweek ?

What is the best solution, because it is in a loop (scan for ...) of a
big DBF.

Fred.

### Week : howto find the date of the first day of a week#

See the DOW() function

It returns 1-7, the default setting has 1 = Sunday, but than can be changed.

Mike

Quote:> Hi everybody !

> Does anybody has to do it ?

> I had to know the first of a week number nweek ?

> What is the best solution, because it is in a loop (scan for ...) of a
> big DBF.

> Fred.

### Week : howto find the date of the first day of a week#

Fred
Given a week number you can calculate the date as January 1 + 7 * weeknumber
and then subtract the day of week returned by DOW(),  to get the date of the
first day of the week.
If you consider Monday the first day of any week you can get the date like
this, using the new DATE(year, month, day) function and assuming you your
week numbering follows the international standard for week numbering: week 1
is the first week with 4 days in the year.

theyear= 1999
theweek= 27
thedate1 = DATE(theyear,1,1)+7* theweek
thedate2 = thedate1 - DOW(thedate1,2)+1
? CDOW(thedate2), thedate2
Monday 1999-07-05

Day 1 of week 1 1999 was Monday Jan 4. Day 1 of week 1 year 2000 will be
Monday, Jan 3.

-Anders

Quote:> Hi everybody !

> Does anybody has to do it ?

> I had to know the first of a week number nweek ?

> What is the best solution, because it is in a loop (scan for ...) of a
> big DBF.

> Fred.

### Week : howto find the date of the first day of a week#

Hi,

Thank you !

Fred

1.
I would like to change the SQL-Server 7 configuration for
- the first day of week to Monday and NOT Sunday AND
- that the first week in a year is the first full week

I know the configuration option "SET DATEFIRST", but this is only for a
session.

I would like to change these settings ONCE for the whole SQL Server.

2.
Found something interesting:
If I want to fill a table with all weeks from 1997 to 2005 and a unique ID,
sometimes,
weeks are missing (2000-01) for example... This problem starts with 2000...
Y2K Problem?
But this problem only happens, if I set the first day of week to Sunday, if
it is set to Monday, it works!

Here is the code for it:
CREATE TABLE [dbo].[DBL_Dates] (
[Number] [T_PCID] NOT NULL,
[Week] VARCHAR(6) NOT NULL
CONSTRAINT [PK_DBL_Dates] PRIMARY KEY ( [Number] )
) ON [PRIMARY]

SET DATEFIRST 7 -- 7 does NOT work, 1 works

BEGIN

BEGIN

END

also... thats why I tried with DAY
END