Jeff Moden

Forum Replies Created

Viewing 15 posts - 30,196 through 30,210 (of 59,101 total)

RE: retrieve only month and year (last two digits) part of datetime field

ColdCoffee (5/2/2012)
Jeff Moden (5/2/2012)
ColdCoffee (5/2/2012)
Jeff, this following produces wrong result for STUFF
SELECT STUFF(CONVERT(VARCHAR(30),'2012-12-12 18:07:33.933',7),4,5,'-')
Result -- 201-12 18:07:33.933
But this produces proper result:
declare @date datetime = '2012-12-12 18:07:33.933'
SELECT STUFF(CONVERT(VARCHAR(30),@date,7),4,5,'-')
I guess, the CONVERT,...
--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 3, 2012 at 6:39 am

#1482962
RE: UPDATE statement using SELECT

Why are we messing around with real names? Single update with random length GUIDs will mask the data with guaranteed no way back.
--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 2, 2012 at 10:00 pm

#1482798
RE: UPDATE statement using SELECT

Why are we messing around with real names? Single update with random length GUIDs will mask the data with guaranteed no way back.
--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 2, 2012 at 9:59 pm

#1482797
RE: Grouping

soulchyld21 (5/2/2012)
Hi I am learning sql and am using the famous northwind database,
I am trying to learn to use aggregate functions such as count()
I am able to get the...
--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 2, 2012 at 7:57 pm

#1482789
RE: retrieve only month and year (last two digits) part of datetime field

ColdCoffee (5/2/2012)
Jeff, this following produces wrong result for STUFF
SELECT STUFF(CONVERT(VARCHAR(30),'2012-12-12 18:07:33.933',7),4,5,'-')
Result -- 201-12 18:07:33.933
But this produces proper result:
declare @date datetime = '2012-12-12 18:07:33.933'
SELECT STUFF(CONVERT(VARCHAR(30),@date,7),4,5,'-')
I guess, the CONVERT, when supplied with...
--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 2, 2012 at 7:40 pm

#1482786
RE: divide column

ColdCoffee (5/2/2012)
But surma.sql, why are u creatin the same thread again and again in all the forums?
Heh... that's easy. It's "urgent". 😉
--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 2, 2012 at 7:31 pm

#1482783
RE: SQL help to CONCATENATE column values

sqlfriends (5/2/2012)
Ok, here is the reduced size sample data.
You should try loading your own script and see what happens when you have no Carriage Returns or Line Feeds in the...
--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 2, 2012 at 7:24 pm

#1482781
RE: retrieve only month and year (last two digits) part of datetime field

Most of us will be in trouble if we go to a country that uses more than 3 character abbreviations for months. 😀
Regardless of language selected (well... at least most...
--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 2, 2012 at 6:54 pm

#1482775
RE: retrieve only month and year (last two digits) part of datetime field

Lynn Pettis (5/2/2012)
Not sure what you would want to do for null values, but this should work for actual date values.
DECLARE @DateVar DATETIME;
SET @DateVar = '20120802 10:30:58.003';
SELECT @DateVar, LEFT(DATENAME(mm,@DateVar),3) +...
--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 2, 2012 at 6:34 pm

#1482772
RE: RS with two date columns from a single date column

If you now have data with something to guarantee the order, such as the IDENTITY column you spoke of, and you would save me some time by posting readily consumable...
--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 2, 2012 at 6:17 pm

#1482765
RE: Orthogonal hierarchies

Mike Austin-398977 (5/2/2012)
There are 321K rows in the Document table and 41K rows in the linking table.
Thanks, Mike.
Mike Austin-398977 (5/1/2012)
I'm not working with a hierarchy, but a "multi-archy". I'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 2, 2012 at 5:38 pm

#1482761
RE: Optimize query for finding nearest airport based on latitude and longitude using STDistance

Given that, I hope your boss doesn't have the misconception that finding the nearest airport to any given airport has anything to with finding the shortest path between airports.
--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 2, 2012 at 6:44 am

#1482326
RE: Database project ranking/prioritization

Chad Crawford (5/1/2012)
We have a variety of database related projects and tasks in the pipeline and I'm trying to put together a system to rank and prioritize them so...
--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 1, 2012 at 10:26 pm

#1482171
RE: Looking for someone to team up with

You have 1.4 million people right here. Much better than teaming up with just 1 person. 😉
--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 1, 2012 at 10:10 pm

#1482165
RE: # of characters

ChrisM@Work (5/1/2012)
Jeff Moden (5/1/2012)
For the same reason you'd distinct a column with dupes. To find out if there are dupes.
LOL! You got me 😛
I had an interview last friday...
--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 1, 2012 at 10:07 pm

#1482164

Viewing 15 posts - 30,196 through 30,210 (of 59,101 total)