martin.edward (2/22/2010)

---

Hello,

I have a strange problem here

SET @sql = (SELECT Code

FROM tablecode where ( @COL= 'Y'))

SELECT @sql

The result I get from this is...

I have written a post on DATEFIRT which can help you understand the usage of it..

http://www.sqlservercentral.com/articles/DateFirst/69203/

Kingston Dhasian (2/24/2010)

---

See if this works..

; WITH cteDBDetails AS

(

SELECT ROW_NUMBER() OVER ( PARTITION BY dbname ORDER BY dbdate DESC ) RDesc,

ROW_NUMBER() OVER ( PARTITION BY dbname ORDER BY dbdate...

Chris Morris-439714 (2/25/2010)

---

Jacco

Assuming there are no gaps in your identity column:

;WITH Results AS (

SELECT pkId, Col1, Col2, Col3, Col4

FROM dbo.tblDemo

WHERE pkId = 1

UNION ALL

SELECT s.pkId,

ISNULL(s.Col1, r.Col1),

ISNULL(s.Col2, r.Col2),...

;with cte1 as

(

select pkid,isnull(col1,-99) as colval,'1' as col

from tblDemo

union all

select pkid,isnull(col2,-99) as col1,'2' as col

from tbldemo

union all

select pkid,isnull(col3,-99) as col1,'3' as col

from tbldemo

union all

select pkid,isnull(col4,-99) as col1,'4'...

Hi anuhya.mudumba

Try this code

; WITH Table1

AS

(

SELECT 1 AS Col1, 'A' AS Col2, 'A1' AS Col3 UNION ALL

...

Most welcome.

declare @s-2 as varchar(100),@sxml as xml,@no as int

set @s-2 ='C-P-M-T-U-R'

set @sxml = cast('<X>'+replace(@s,'-','</X><X>')+'</X>' as xml)

set @no =2

select v

from

(

select d.v.value('.','varchar(50)') as v,Row_number() OVER (order by (select 1))...

I tried running the same code as

declare @jcactdate as int,@jcactime as int

select @jcactdate=110054,@jcactime=231111

select

cast(

cast(1900+cast(((@jcactdate-(@jcactdate%1000))/1000) as int) as varchar(4))

+ ' ' + substring(cast (@jcactime as varchar(6)), 1, 2)

+ ':'...

Alan Stanley (2/23/2010)

---

Can anybody point me in the right direction, I am using replace on a nvarchar(max) field but the results are getting truncated. I have tried casting all the...

jamesburkill (2/23/2010)

---

Ok. You've won me over.

I just did A quick test on the efficiency of each method.

Using the script below, my method took 2:20 to 2:30 to execute. ...

Brian Munier (2/19/2010)

---

davidgr144 (2/19/2010)

---

This is a great tip. Thanks.

Can I raise a question regarding it though?

Maybe I'm misreading but if the date you pick is Saturday (instead of Thursday...

You have to make a bit change in the script, as per...

Yes Patrick. You can even do like

SELECT (DATEPART(dw, '2009-12-17') + @@DATEFIRST-1) % 7 AS dw

For Monday to be the first day..:)

Thanks Knut. I have cleared myself later on regarding the...

Thanks Kelsey.

Yes i might be mistaken, the format is American not English.

Btw, even i like that Japanese format.

Well, its quite easy to modify the script to get the...