Day of Week -- sql server dts

JasonB
5/9/2007 9:39:01 AM

Hi, I've been struggling with returning a day of week number.
I want to return a 1 for Sunday and a 7 for Saturday.

I thought this would be easy with a derived column task and using the
DATEPART function with "dw" as the parameter.

However I've found that this method is Language dependant, and the integer
returned can differ based on the user.

So, I came up with the following to provide a language independant method:

--TODAY!
DECLARE @Date3 datetime
SET @Date3 = '2007/05/09'
Select (DATEPART(dw, @Date3) + @@DATEFIRST - 1) % 7 + 1 As DayNo

In a derived column, I'd just drop the date variable and replace it with the
date column.

However.... the @@DATEFIRST system variable doesnt seem to be available, and
the statement wont parse.

Anybody that can help with this? Solutions that dont use the script tack

Russell Fields
5/9/2007 4:36:24 PM

Jason,

If you subscribe to SQL Server magazine www.sqlmag.com Itzik Ben-Gan has
been running a series on datetime data, which includes some guidance on
language independent date math. (However, you have to be a subscriber to
read the details.)

RLF
[quoted text, click to view]

sql server dts : Day of Week