Can there only ever be be one row with a NULL ChangeDate, with this row belonging to the most recent group?

If this is not the case, then how can you...

CREATE TABLE #MyTempTable (date_field datetime)

INSERT INTO #MyTempTable(date_field)

SELECT '2010-06-24T18:19:00.000' UNION ALL

SELECT '2010-06-20T01:50:00.000'

UPDATE #MyTempTable

SET date_field = DATEADD(day, DATEDIFF(day, '1753-01-01T00:00:00.000', date_field), '1753-01-02T06:59:00.000')

WHERE DATEPART(hour, date_field) >= 7

Averaging a date column is a fairly unusual thing to want to do, but if you really want to do this, it is achievable by first converting the datetime to...

Amusingly, any sequence of unary operators before an integer (as long as two dashes together are avoided) seems to be valid in TSQL:

DECLARE @a int

SET

There is a standard mathematical expression for the sum of all integers from 1 to N.

Sum(R) {R = 1..N} = N * (N + 1) / 2

So you could just...

It's a simple change to your existing datetime expressions:

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 15, 0)

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 12, 0)

22800 = 1900 * 12

so

DATEADD(MONTH, 12 * @Year + number - 22801, 6)

is equivalent to

DATEADD(MONTH, 12 * (@Year - 1900) + number - 1, 6)

but saves...

Here is your test data in a readily consumable form, i.e. a CREATE TABLE statement and an INSERT statement. Supplying test data in this form will encourage people to assist...

Here's another solution using PATINDEX and STUFF.

EDIT: Just noticed my solution was identical to Chris Morris' so have removed it.

Does this return anything on your database?

select payrolldate,

Sunday = dateadd(dd, ((datediff(dd, '17530107', payrolldate)/7)*7)+7, '17530107')

from payroll

You have a GROUP BY clause but since you have no aggregate functions its only effect is to eliminate duplicate rows.

It would help if you could describe what you trying...

How do you define the sequential order of rows in this table?

Is there a suitable datetime column or an incrementing integer column?

Black is a colour in the same way that...

You are probably not getting the results you are expecting because AND comes above OR in the operator precedence table. I've added brackets to show how your WHERE clause is...

UNPIVOT does precisely what you're looking for.

SELECT [EmployeeNumber], [Security], [Level]

FROM [dbo].[GblSecurityMain]

UNPIVOT (

[Level] FOR [Security]

IN ([Development], [Time], [Inventory], [Renewal], [Support], [Results])

)...