Date, VBA vs MS-SQL

[Morten Snedker]

I have records with the date 01/01/2005

Here in Denmark Janurary 1. has week 53.

Select * From Book_Tmp Where Datepart(week,Dato)=53

returns 0 records, hence BOL:

January 1 of any year defines the starting number for the week
datepart, for example: DATEPART(wk, 'Jan 1, xxxx') = 1, where xxxx is
any year.


How do I work around this problem?


/Snedker

 

[Ivan Arjentinski]

Morten,

In order to try answering your question, please clarify:

Is Jan 2nd also week 53?
If Jan 3rd also week 53?
When you say "1/1/2005 is week 53", do you mean week 53 of 2004 or week 53
of 2005 or what?
....
Please define when it finishes counting weeks from the previous year.

 

[David Portas]

Look at the CREATE FUNCTION topic in Books Online. There is an example of an
ISO week number function which should give the results you want.

 

[Morten Snedker]

In order to try answering your question, please clarify:

Is Jan 2nd also week 53? Yes

If Jan 3rd also week 53? No

When you say "1/1/2005 is week 53", do you mean week 53 of 2004 or week 53
of 2005 or what?

Sorry for the typo: 1/1/2004 is week 53.

Please define when it finishes counting weeks from the previous year.

Week 1 starts in the first week containing minimum 4 days. Using this
calendar, week 1 started

1999: 04-Jan
2000: 03-jan
2001: 01-jan
2002: 31-dec
2003: 30-dec
2004: 29-dec
2005: 03-jan

/Snedker

 

[John Bell]

Hi

In addition to the other posts you may find a calendar table useful in this
situation.
http://www.aspfaq.com/show.asp?id=2519

John

 

[Morten Snedker]

On Mon, 27 Dec 2004 12:00:23 -0000, "David Portas"

That was the I was looking for. Thanks for your time and input.