How to catch even and odd weeks?

Question

How to catch even and odd weeks?

Edvard Korsbæk

SSCertifiable

Points: 5991
More actions
November 10, 2014 at 6:26 am

#291797

Hi all!
'
I have a date stored in a field dato [DATETIME]
How do i catch about this date is in an even or in an odd week ( ISO 8601 based).
I think it will be nessecary to start with:
Set Datefirst 1
to be sure, which setting is the starting point - USA weeknumbers are not allways the same as europaen!
need it for a where clause, where i want only to catch dates in even or odd weeks.
Best regards
Edvard Korsbæk

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

Edvard Korsbæk (11/10/2014)
Hi all!
'
I have a date stored in a field dato [DATETIME]
How do i catch about this date is in an even or in an odd week ( ISO 8601 based).
I think it will be nessecary to start with:
Set Datefirst 1
to be sure, which setting is the starting point - USA weeknumbers are not allways the same as europaen!
need it for a where clause, where i want only to catch dates in even or odd weeks.
Best regards
Edvard Korsbæk

Your post is not exactly clear. What you do you mean by "catch" dates in even or odd weeks. Last time I checked all weeks are either even or odd. 😉 Are you trying to query this table to get even or odd weeknumbers? If that is the case use modulo. If you can explain more clearly what you are trying to do we can help.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Edvard Korsbæk SSCertifiable Points: 5991 More actions · Answer 2

I hope this is more clear:

What i want is the where clause for:

Select count(*) from dbo.dutyrostershift where (dato in an even week)

I have this table:

CREATE TABLE [dbo].[dutyrostershift](

[dato] [datetime] NULL,

[std] [tinyint] NULL,

[specialvagt] [tinyint] NULL,

[daekbemand] [tinyint] NULL,

[extratimer] [int] NULL,

[overarbtimer] [int] NULL,

[manuel] [tinyint] NULL,

[beskyttet] [tinyint] NULL,

[confirmed] [tinyint] NULL,

[vacationtype] [varchar](50) NULL,

[breakswish] [tinyint] NULL,

[dutyrosterid] [int] NULL,

[employeeid] [int] NULL,

[employeegroupid] [int] NULL,

[childforcaredayid] [int] NULL,

[originatingstaffingrequirementid] [int] NULL,

[shifttype] [int] NULL,

[fromtime] [int] NULL,

[totime] [int] NULL,

[id] [int] IDENTITY(1,1) NOT NULL,

[LeaveType_Id] [int] NULL,

[LoginID] [int] NULL,

[StatusNo] [int] NULL,

[Time_Stamp] [datetime] NULL,

[Comment] [char](120) NULL,

[Is_Free_sat] [tinyint] NULL,

[Is_Center_Opening] [tinyint] NULL,

[is_fo_day] [tinyint] NULL,

[SavedDuty_Id] [int] NULL,

[mTid_Id] [int] NULL,

[duty_released] [tinyint] NULL,

[recordstatus] [tinyint] NULL,

CONSTRAINT [pk_dbo_pk_dutyrostershift] PRIMARY KEY CLUSTERED

(

[id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Sean Lange SSC Guru Points: 286573 More actions · Answer 3

As I said you need to use modulo here.

Something like this.

where datepart(week, dato) % 2 = 0

Please note the above is nonsargable and will force a scan on the entire table.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Edvard Korsbæk SSCertifiable Points: 5991 More actions · Answer 4

Edvard Korsbæk

SSCertifiable

Points: 5991

November 10, 2014 at 7:26 am

#1757423

Thanks - really simple.

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 5

If it's supposed to be based on ISO week number, substitute the ISOWEEK datepart in Sean's good code above.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)