If you really do want that specific format, you have two options: 1) Manipulate the string without converting, or 2) convert to datetime and then back to a string.

SELECT dt.dt_string

,...

LAG was introduced in SQL 2012. This error indicates...

First, I...

Alan.B (8/13/2015)

---

drew.allen (8/13/2015)

---

Alan.B (8/13/2015)

---

Just to add to Drew's excellent solution, not only would you want to use the value() method over the query method here, you should include the text()...

Alan.B (8/13/2015)

---

Just to add to Drew's excellent solution, not only would you want to use the value() method over the query method here, you should include the text() node in...

Instead of trying to shred them separately and then put them together, shred them together and separate them.

DECLARE @XML TABLE (XMLData XML);

DECLARE @Person_Tasks TABLE( person_name NVARCHAR(50), person_address NVARCHAR(50), task_name NVARCHAR(50),...

This works by creating a "loop" in a random order and assigning the previous person in the loop as the reviewer.

DECLARE @temp TABLE (id int,logonid varchar(15),reviewer varchar(15))

insert into @temp

VALUES(1,'personA',NULL)

,(2,'personB',null)

,(3,'personC',null)

,(4,'personD',null)

,(5,'personE',null)

,(6,'personF',null)

;

WITH...

This is a faster version, because it only requires one sort.

with TestData as (

select memid, MonthStartDate

from

(values

(123,'2014-01-01'),

(123,'2014-03-01'),

(123,'2014-04-01'),

(123,'2014-05-01'),

(123,'2014-06-01'),

(123,'2014-07-01'),

(123,'2014-08-01'),

(123,'2014-09-01'),

(123,'2014-10-01'),

(123,'2014-11-01'),

(123,'2014-12-01'),

(222,'2014-01-01'),

(222,'2014-02-01'),

(222,'2014-03-01'),

(222,'2014-04-01'),

(222,'2014-05-01'),

(222,'2014-06-01'),

(222,'2014-07-01'),

(222,'2014-08-01'),

(222,'2014-09-01'),

(222,'2014-10-01'),

(222,'2014-11-01'),

(223,'2014-02-01'),

(223,'2014-03-01'),

(223,'2014-04-01'),

(223,'2014-05-01'),

(223,'2014-06-01'),

(223,'2014-07-01'),

(223,'2014-08-01'),

(223,'2014-09-01'),

(223,'2014-10-01'),

(223,'2014-11-01'),

(223,'2014-12-01'))dt(memid,MonthStartDate)),

BaseData as (

SELECT

*,

DATEDIFF(MONTH, LAG(MonthStartDate, 10) OVER(PARTITION BY memid ORDER BY MonthStartDate), MonthStartDate) AS MonthDiff

FROM...

As an added note, REVERSE should generally only be used when it is easier to define your criteria reading from the right than it is reading from the left. ...

You can't do this. Foreign Keys have to reference a unique record. That's what the KEY means in foreign key. Vendor_ID by itself is insufficient to uniquely identify a...

You only need three replaces.

WITH CTE(col) AS

(

SELECT

'ServerCentral|||||forum|||||||||||||||is||||||the||best'

UNION

SELECT 'so||||||be|||||on||||||||||||||||||||||||||||||||||||||||||||it'

UNION

SELECT 'An||||additional|test||with|occurences||||of|a|||||single|||||pipe.'

)

SELECT REPLACE(REPLACE(REPLACE(col, '|', '|='), '=|', ''), '|', '')

FROM CTE

The previous solutions treat an odd number of pipes and an even...

I agree with Luis, the issue is that you are using loops in the first place. I didn't have time to try to rewrite it without using loops, so...

I disagree. My script succeeded marvelously. It found records that you never would have found with...

Luis Cazares (8/4/2015)

---

Is '2015/01/01' a fixed value for the query or is it a value that can change from one row to another?

If it's a scalar value, you should change...

The WHERE clause is processed before the SELECT statement, therefore any aliases assigned in SELECT statements are not available for their corresponding WHERE clauses. I have gotten around this...