Changing smalldatetime to day of weekdaynumber T-SQL??

[joethehoe]

Hello ALL I'm new to SQL and I'm trying change the results of my query to a week day number instead of a smalldate: (IE SUNDAY would 1. and monday 2) I know its a datefirst function but I'm unable to get the correct syntax ??

DECLARE @begin smalldatetime,
@end smalldatetime

SET @begin = '08/11/03'
SET @end = '08/11/03'

SELECT J.jobdate,
AZ_Time = CASE AU.[sysName]
WHEN 'Ratbert' THEN RIGHT('0' + CAST(CAST(LEFT(AU.tdTime,2) as int)-2 as varchar) + RIGHT(AU.tdTime,6),8)
WHEN 'Dogbert' THEN RIGHT('0' + CAST(CAST(LEFT(AU.tdTime,2) as int)-2 as varchar) + RIGHT(AU.tdTime,6),8)
ELSE AU.tdTime
END,
SUM(AU.jobClock)/3600 AS jobClock
FROM Analyst.mo_agent_unit
AS AU INNER JOIN
Analyst.mo_job
AS J ON J.[sysName] = AU.[sysName] AND
J.jobNum = AU.jobNum
WHERE (CAST(J.jobDate as datetime) BETWEEN @begin AND @end) and
(j.jobName like 'AG3%')and au.agentID LIKE 'w%'
GROUP BY J.jobDate,
CASE AU.[sysName]
WHEN 'Ratbert' THEN RIGHT('0' + CAST(CAST(LEFT(AU.tdTime,2) as int)-2 as varchar) + RIGHT(AU.tdTime,6),8)
WHEN 'Dogbert' THEN RIGHT('0' + CAST(CAST(LEFT(AU.tdTime,2) as int)-2 as varchar) + RIGHT(AU.tdTime,6),8)
ELSE AU.tdTime
END
ORDER BY J.jobDate, AZ_Time

 

[clarkin]

Not sure what bit in that uber select you are referring to - however:

from BOL (

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_syntaxc_9kvn.asp)

for DATEPART:

use DATEPART(dw, someDatetimeValue) to get a number 1 to 7 representing day of the week

Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].