Jeff Moden

Forum Replies Created

Viewing 15 posts - 58,291 through 58,305 (of 59,067 total)

RE: Week number of the month

Noeld,
I haven't figured a way around it yet but the function needs to return a "5" instead of the "1" it does for 02/01/2006 according to what the user posted...
--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)

April 19, 2006 at 5:46 pm

#633160
RE: how to know the size in bytes of a record in a table

Different slant...
EXEC sp_SpaceUsed yourtablenamehere
--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)

April 19, 2006 at 5:26 pm

#633159
RE: Parsing question in T-SQL ---- PLEASE HELP!!

This may be a little faster... replace the #30 with the defined width of the column (or more)...
SELECT SUBSTRING(somevarcharcol,PATINDEX('%[^0]%',@String),30)
FROM yourtable
--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)

April 19, 2006 at 5:12 pm

#633157
RE: SUBSTRING a DATETIME value in a SELECT statement

Trys this instead...
SELECT CONVERT(VARCHAR(10),date,101)
... because the supbstring method on date castings will likely not five you the mm/dd/yyyy format that I think you're looking for. And here's some other formating...
--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)

April 19, 2006 at 5:01 pm

#633156
RE: turn time in excel into epoch time for sql server 2000

...and thank you for the feedback...
--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)

April 18, 2006 at 8:43 pm

#632993
RE: turn time in excel into epoch time for sql server 2000

Or... don't let it default to INT...
declare @DateOfInterest datetime,
@EpochDate datetime
set @DateOfInterest = '12/11/1962'
set @EpochDate = '1/1/1970 03:00'
select CAST((@DateOfInterest - @EpochDate) AS DECIMAL(24)) *24 *60 *60 *1000 as n
--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)

April 17, 2006 at 9:38 pm

#632884
RE: query to find out last date of the previous month

Short, simple, nasty fast because there are no VARCHAR conversions...
SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)-1
--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)

April 17, 2006 at 9:29 pm

#632881
RE: Question on select execution

I agree... I always spell them out all the way...
I don't believe BOL calls them "alias names" though.
--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)

April 17, 2006 at 9:26 pm

#632880
RE: How can I drop all the constraints in a database

The scripts offered will certainly work (although you may want to add WHERE so.XTYPE = 'U' to filter on tables only), but tell me please... why would you want to...
--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)

April 16, 2006 at 9:14 pm

#632724
RE: Question on select execution

Not quite true... JOIN is an acceptable shortcut for INNER JOIN. Additionally, LEFT JOIN, RIGHT JOIN, and FULL JOIN are acceptable shortcuts for the similarly named OUTER counterparts...
USE Northwind
SELECT...
--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)

April 16, 2006 at 9:03 pm

#632723
RE: checking if a stored proecures parameters are null or empty

Ben,
Why not make the ConsultancyBookingNumber column in the Consultancy table and IDENTITY column instead of using MAX+1? Then, you wouldn't even need this procedure as the column would be auto-numbering...
--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)

April 16, 2006 at 8:44 pm

#632722
RE: Copy tables to another server

Nicely done... You should publish this in the "scripts" section of this forum.
--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)

April 16, 2006 at 8:33 pm

#632721
RE: Truncating Date

Although the DATEADD/DATEDIFF method will sometimes return a faster duration, it is slower than "convert(datetime,convert(int,GetDate()-.5))" if you consider actual resources used like CPU Seconds and DIsk Reads and Writes. Here's...
--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)

April 15, 2006 at 11:13 pm

#632704
RE: Truncating Date

I just can't bring myself to trust anything in an article where the author makes mistakes like the following...
"Remember that the BETWEEN clause retrieves values that are equal...
--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)

April 15, 2006 at 10:22 pm

#632703
RE: Generating a Range

David wrote... "If I was to use the static table approach I would probably stick it in its own database so that it wouldn't artificially inflate the backups."
Yep, I know...
--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)

April 14, 2006 at 6:19 pm

#632686

Viewing 15 posts - 58,291 through 58,305 (of 59,067 total)