ChrisM@Work

Forum Replies Created

Viewing 15 posts - 9,331 through 9,345 (of 10,143 total)

RE: Please help me see what is happening in datetime string manipulation

Garadin (11/28/2008)
Heh, so the "local settings" were... a different time. :blush:
It's those pesky CHARs innit!
Sue, try this instead:
SELECT RIGHT('0' + CAST(datepart(hh,@Now) AS VARCHAR(2)),2)
+ RIGHT('0' + CAST(datepart(mi,@Now) AS VARCHAR(2)),2)
Cheers
ChrisM
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 28, 2008 at 9:21 am

#904870
RE: Please help me see what is happening in datetime string manipulation

Sue (11/28/2008)
09 45 2 2 ...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 28, 2008 at 9:05 am

#904863
RE: How to convert datatype varchar to int?

Shree, try this:
ORDER BY State, RIGHT('000000000'+LTRIM(Route), 10), Rank
Try it without the LTRIM too, it may not be necessary.
Cheers
ChrisM
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 28, 2008 at 8:40 am

#904837
RE: Help Calculating Percentages

Hi Jody
Something like this?
DROP TABLE #CourseResults
CREATE TABLE #CourseResults (CourseNum VARCHAR(7), Grade CHAR(1))
INSERT INTO #CourseResults (CourseNum, Grade)
SELECT '0500300', 'A' UNION ALL
SELECT '0500300', 'B' UNION ALL
SELECT '0500300', 'A' UNION ALL
SELECT '0500300', 'C'...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 28, 2008 at 8:25 am

#904825
RE: random integer number scalar function

Jeff Moden (11/28/2008)
That's about what I would expect from an almost real set of random numbers.
Sorry Jeff, should have explained more:
SELECT Number, COUNT(*) as NumberCount
FROM (SELECT ABS(CHECKSUM(NEWID()))%(500-300+1)+300 AS Number
FROM dbo.Numbers
WHERE...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 28, 2008 at 8:03 am

#904805
RE: random integer number scalar function

Jeff Moden (11/28/2008)
That's about what I would expect from an almost real set of random numbers.
You mean almost reel, right? 😛
-- did you catch the dupes?
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 28, 2008 at 7:57 am

#904803
RE: random integer number scalar function

Now this is interesting. Watch what happens when you try to find out how many of those random integers are randomly repeated:
SELECT Number, COUNT(*)
FROM (SELECT ABS(CHECKSUM(NEWID()))%(500-300+1)+300 AS Number
FROM dbo.Numbers
WHERE...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 28, 2008 at 7:38 am

#904784
RE: Functio to get Amount in words.

rbarryyoung (11/28/2008)
Oh it makes sense here, colloquially it's used all the time, it just isn't acceptable in financial English and text (the usual place for numbers-to-English transformations).
A quick Google returned...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 28, 2008 at 7:23 am

#904774
RE: Error,while inserting data into Excel from sql server 2005!!!!!!

Here's an OPENROWSET Excel insert which has been working for a while:
-- INSERT the results into the newly-created spreadsheet
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=\\ReportingServer\D$\EDIInvoices.xls',
...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 28, 2008 at 7:14 am

#904771
RE: Functio to get Amount in words.

rbarryyoung (11/28/2008)
Really? "One hundred and One" is acceptable in the UK? That really surprises me.
Yep. Even the sample you posted above makes perfect sense to us:
nine hundred and...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 28, 2008 at 5:43 am

#904738
RE: SP Error - String or binary data would be truncated

pri.amin (11/28/2008)
Ok getting the following error when running the update:
Msg 8152, Level 16, State 4, Line 1
String or binary data would be truncated.
The statement has been terminated.
Looks like you may...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 28, 2008 at 5:35 am

#904736
RE: SP Error - String or binary data would be truncated

pri.amin (11/28/2008)
Sorry, what did you mean by the bit of code:
DECLARE @Client VARCHAR(255)
SET @Client = 'This is a 30-char client name.Here are another thirty chars.'
This is just sample data, in...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 28, 2008 at 5:30 am

#904733
RE: Merging two resultsetfrom same table in sql server 2005

This should do the trick:
[font="Courier New"]DROP TABLE #IssueMst
CREATE TABLE #IssueMst (issueid INT, status CHAR(1), ownedby VARCHAR(2), [desc] VARCHAR(3))
INSERT INTO #IssueMst (issueid, status, ownedby, [desc])
SELECT 1, 'A', '1', 'xyz' UNION ALL
SELECT...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 28, 2008 at 5:26 am

#904732
RE: Cannot get value from SELECT statement

Thanks Johann - more than one row returned would have indicated a slightly different table-based solution. Anyway, reckon this should do the trick:
[font="Courier New"]SELECT @BuildID = BuildID
FROM Builds_LIVE
WHERE BranchID...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 28, 2008 at 4:58 am

#904714
RE: SP Error - String or binary data would be truncated

Like this:
[font="Courier New"]INSERT INTO dbo.tblClients
   (Client_Short_Name,
   Client_Long_Name,
   CountryCode,
   ClientID)
SELECT
   CASE WHEN LEN(c.Client) > 30 THEN LEFT(c.Client, 27) + '...' ELSE c.Client END AS Client,
   CASE WHEN LEN(c.Client) >...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

November 28, 2008 at 4:44 am

#904701

Viewing 15 posts - 9,331 through 9,345 (of 10,143 total)