Record missing using work week query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Record missing using work week query

Author

Topic

lsy
Yak Posting Veteran

57 Posts

Posted - 2007-02-15 : 20:23:45

I got a problem regarding query work week in database.
The database contain data of year 2006 and 2007. When i query about work week, some record is missing.

i wrote the sql statement as
SELECT * FROM TBL WHERE FORMAT(MYDATE, 'WW', 1, 2) = 1 AND YEAR(2007);

There is one record missing... which is 31/12/2006 record.

So any idea to eliminate this??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-16 : 02:59:37

AND YEAR(2007) what?

Peter Larsson
Helsingborg, Sweden

lsy
Yak Posting Veteran

57 Posts

Posted - 2007-02-16 : 03:07:13

sorry typo...
Year(MYDATE) = 2007

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-16 : 03:08:20

quote:
Year(MYDATE) = 2007
There is one record missing... which is 31/12/2006 record.

What would you expect ? ? You wanted 2007 records only

lsy
Yak Posting Veteran

57 Posts

Posted - 2007-02-16 : 03:11:21

i want work week 1 in year 2007 record... which is include 31/12/2006.
but what i query is not.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-16 : 03:15:15

what is your definition of a week ? Start from Sunday - Saturday ? Monday - Sunday ?

lsy
Yak Posting Veteran

57 Posts

Posted - 2007-02-16 : 03:16:26

should be sunday.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-16 : 04:05:28

And the first week of the year is defined as what?

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-16 : 04:29:57

So 31/12/2006 is in last week of 2006 or 1st week of 2007 ?

Why not create your own calendar table and define your own week no in the calendar table.

lsy
Yak Posting Veteran

57 Posts

Posted - 2007-02-16 : 04:44:14

quote:
Originally posted by Peso

And the first week of the year is defined as what?

Peter Larsson
Helsingborg, Sweden

the MYDATE column is defined as date...
so i would like to query ww 01-2007 which will return from 31/12/2006 till 6/1/2007... but it just return me 1/1/2007 till 6/1/2007. it missout 31/12/2006

lsy
Yak Posting Veteran

57 Posts

Posted - 2007-02-16 : 04:45:17

quote:
Originally posted by khtan

So 31/12/2006 is in last week of 2006 or 1st week of 2007 ?

Why not create your own calendar table and define your own week no in the calendar table.

KH

but the year is not there... it might mixxed up with other year record.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources