The value that datepart(DW,MyDate) returns depends on the setting of datefirst.

This code will always return a value of 0 for Monday, 1 for Tuesday,...,  6 for Sunday, no matter what the setting of datefirst is.

select datediff(dd,'17530101',MyDateColumn)%7

If you want your week to start with 0 for Sunday, 1 for Monday, etc., use this:

select (datediff(dd,'17530101',MyDateColumn)+1)%7

If you want the weekdays to be numbered from 1 to 7, instead of 0 to 6, add 1 to the results above:

select (datediff(dd,'17530101',MyDateColumn)%7)+1