Help to Convert date to week.

[jvincent]

Hi,
I'm looking for a module wich converts a date to a week.
Ex: i type 08/02/2005 and i obtain Week #2
(Some years have 52 and other 53 weeks, depend on the date of the 1st week of the year, an other problem is that some people say the week begins the monday, other say it begins a sunday)
Maybe a built in fonction exists.
Thanks in advance for help.
VINCENT

 

[bvan]

Use the DatePart function. You can return the week number by using the "ww" interval.



DatePart(interval, date[,firstdayofweek[, firstweekofyear]])

The DatePart function syntax has these named arguments:

Part Description
interval Required. String expression that is the interval of time you want to return.
date Required. Variant (Date) value that you want to evaluate.
firstdayofweek Optional. A constant that specifies the first day of the week. If not specified, Sunday is assumed.
firstweekofyear Optional. A constant that specifies the first week of the year. If not specified, the first week is assumed to be the week in which January 1 occurs.
Settings

The interval argument has these settings:

Setting Description
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second
The firstdayofweek argument has these settings:

Constant Value Description

vbUseSystem 0 Use the NLS API setting.
vbSunday 1 Sunday (default)
vbMonday 2 Monday
vbTuesday 3 Tuesday
vbWednesday 4 Wednesday
vbThursday 5 Thursday
vbFriday 6 Friday
vbSaturday 7 Saturday
The firstweekofyear argument has these settings:

Constant Value Description

vbUseSystem 0 Use the NLS API setting.
vbFirstJan1 1 Start with week in which January 1 occurs (default).
vbFirstFourDays 2 Start with the first week that has at least four days in the new year.
vbFirstFullWeek 3 Start with first full week of the year.
Remarks

You can use the DatePart function to evaluate a date and return a specific interval of time. For example, you might use DatePart to calculate the day of the week or the current hour.
The firstdayofweek argument affects calculations that use the "w" and "ww" interval symbols.
If date is a date literal, the specified year becomes a permanent part of that date. However, if date is enclosed in double quotation marks (" "), and you omit the year, the current year is inserted in your code each time the date expression is evaluated. This makes it possible to write code that can be used in different years.

 

[Smart]

Re

You could also use this in the query

week: Format([Yourdate],"ww")

 

[jvincent]

Smart, Bvan thank you for reponses, i will try.

 

[jvincent]

Smart, format function works fine, thx EOM

Smart, format function works fine
Thank you.
VINCENT

 

[bvan]

Keep in mind that a using "Format" does not change the underlying data. If the date that you are formatting is a long date, you would get some ugly results if you grouped by it in a query. Both methods will work depending on your needs.

 

[Bat17]

also make sure that you use the optional values to control the First Day of week, First Week of Year or you may not return the number you expect.

Peter

 

[jvincent]

Oh! i will make more tests, what is the syntax if want the week start a monday.
Thanks in advance and thank for avertissements.

 

[Bat17]

The normal uk setting for week starting Moday is
DatePart(ww, date,2,2)

Week 1 will be the first week with at least 4 days in it.
So week 1 2005 starts 3 jan 2005

HTH

Peter

 

[jvincent]

Thanks Bat