Jeff Moden

Forum Replies Created

Viewing 15 posts - 43,621 through 43,635 (of 59,063 total)

RE: Last number from a Text Field

arun.sas (5/29/2009)
Spot on, Ramesh. And, you made it easy to convert to the MONEY datatype so you can use the result number for calculations.
Hi jeff,
Ok with the Ramesh statements,
But,...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

May 30, 2009 at 1:01 pm

#1002414
RE: Last number from a Text Field

Ramesh (5/29/2009)
See if this helps.
; WITH TestCTE
AS
(
SELECT'FFX Buy USD/Sell AUD, Broker 703 MATURITY 11/05/09 SOLD AUD 18,669,911.96' AS SomeText
UNION ALL
SELECT'FFX Buy $/Sell $, Broker 471 MATURITY 11/05/09 SOLD $878.96' AS...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

May 29, 2009 at 7:29 pm

#1002342
RE: Data Archiving

Shamshad Ali (5/27/2009)
We found that the bottlneck is SQL Server all the time.
Nope... it's not SQL Server. It's the code that's running against SQL Server. You say you've...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

May 29, 2009 at 6:43 pm

#1002338
RE: The Sports Salary

Would you like a less subjective, and more open salary system?
Nope... I don't want anyone to know how much more I make than them. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

May 29, 2009 at 6:20 pm

#1002335
RE: Select Data in XML

Heh... "XML is case sensitive"... one more reason for me to keep it out of my databases. 😛
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

May 29, 2009 at 6:14 pm

#1002334
RE: Convert string to human readable date/time format

Jack Corbett (5/29/2009)
I like it Jeff. I always forget about STUFF(). It's a great function.
Thanks Jack. I appreciate the feedback. Just remember.... STUFF right to left...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

May 29, 2009 at 5:52 pm

#1002329
RE: Date Function for SQL Server 6.5

Oh, yeah.... the other thing to try is changing the "0" date to '1900-01-01' everywhere.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

May 29, 2009 at 4:04 pm

#1002307
RE: Date Function for SQL Server 6.5

You appear to be missing part of the query after the AND. What's the full query look like?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

May 29, 2009 at 4:03 pm

#1002306
RE: Converting Character to DATETIME

Different take on the same thing...
DECLARE @DateString VARCHAR(50)
SET @DateString = '2009-02-26T00:00:00.0000000-08:00'
SELECT CONVERT(DATETIME,LEFT(@DateString,23),126)
+ CAST(RIGHT(@DateString,5) AS DATETIME)
I couldn't remember if you...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

May 29, 2009 at 3:54 pm

#1002305
RE: count spaces between two strings

ukumawat (5/29/2009)
hi all,
is there any method to count number of spaces between two strings?
thanks in advance.
Andrew's method will work just fine, but I have to ask because this sounds like...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

May 29, 2009 at 3:33 pm

#1002302
RE: Trying to get the differance between two dates

andrewd.smith (5/29/2009)
Lookup DATEDIFF TSQL function in SQL Server Books Online
Heh.... try it, Andrew. 😉 You may be surprised how difficult that recommendation actually is.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

May 29, 2009 at 3:29 pm

#1002301
RE: Process text files according to it's content

Do you have to use SSIS for this? Or, can you use xp_CmdShell?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

May 29, 2009 at 3:25 pm

#1002298
RE: Convert string to human readable date/time format

I took a slightly different tact...
DECLARE @DateString VARCHAR(50)
SET @DateString = '20080916131411.000000-300'
SELECT CONVERT(DATETIME,LEFT(STUFF(STUFF(STUFF(@DateString,13,0,':'),11,0,':'),9,0,' '),21))
+ DATEADD(mm,CAST(SUBSTRING(@DateString,PATINDEX('%[-+]%',@DateString),50) AS INT),0)
Can't remember if you're...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

May 29, 2009 at 3:19 pm

#1002296
RE: Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

Thanks for the awesome compliment, Ravi. I'll try.
BTW, I really like your signature line. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

May 29, 2009 at 7:41 am

#1002049
RE: Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

Cool... although I don't believe you need to change the datatype on the IDENTITY column. You're only working on 10 million rows. INT goes up to 2 billion....
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

May 28, 2009 at 12:18 pm

#1001642

Viewing 15 posts - 43,621 through 43,635 (of 59,063 total)