Jeff Moden

Forum Replies Created

Viewing 15 posts - 24,751 through 24,765 (of 59,099 total)

RE: Calculating Duration Using DATETIME Start and End Dates (SQL Spackle)

Thomas Abraham (1/16/2014)
I meant to post this earlier, but got caught up in some of the tangent. Thanks to Jeff for providing this spackle. Great job, as always.
Thanks for 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)

January 16, 2014 at 9:07 pm

#1680951
RE: Calculating Duration Using DATETIME Start and End Dates (SQL Spackle)

rho_pooka (1/16/2014)
Thank you for article, enjoyed the read!
Thank you very much for the feedback. I appreciate it.
--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)

January 16, 2014 at 9:04 pm

#1680950
RE: Calculating Duration Using DATETIME Start and End Dates (SQL Spackle)

dwain.c (1/16/2014)
I must say, I love the technique Jeff! Very simple, straightforward and clean with good performance. Nothing more straightforward comes immediately to mind (issues noted above aside).
Normally...
--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)

January 16, 2014 at 8:53 pm

#1680947
RE: Calculating Duration Using DATETIME Start and End Dates (SQL Spackle)

Peter E. Kierstead (1/16/2014)
[h4]Try this...[/h4]
Create Function dbo.ElapsedTime
(
@StartTime DateTime,
@EndTime DateTime
)
Returns VarChar(20)
As
Begin
Declare
...
--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)

January 16, 2014 at 8:35 pm

#1680945
RE: Calculating Duration Using DATETIME Start and End Dates (SQL Spackle)

mayor 76640 (1/16/2014)
My specific use of the datetime field is to see if it occurs
within a user specified date range.
The examples I received with the stored procedures (from software application...
--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)

January 16, 2014 at 8:08 pm

#1680942
RE: Calculating Duration Using DATETIME Start and End Dates (SQL Spackle)

Thomas Abraham (1/16/2014)
Oliver Gugolz (1/16/2014)
Here is my simple method to format the output to a more readable format:
CAST(GETDATE() - @StartDT AS TIME(2)) AS Duration
But that only displays the fractional hours...
--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)

January 16, 2014 at 8:06 pm

#1680941
RE: Calculating Duration Using DATETIME Start and End Dates (SQL Spackle)

Eirikur Eiriksson (1/16/2014)
Thank you for the spackle Jeff but it looks like there is a small crack in it as it makes the assumption that the end time is always...
--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)

January 16, 2014 at 8:03 pm

#1680940
RE: BCP export to csv

cdutre (1/16/2014)
I hate replying to an old thread but I ran into the same problem and was able to resolve it.
Once I eliminated any field values that could...
--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)

January 16, 2014 at 1:45 pm

#1680852
RE: Deprecated]Function call can be extracted from the WHERE clause to avoid unnecessary table scan.

Welsh Corgi (1/16/2014)
Lowell (1/16/2014)
dateadd is not deprecated.
I run it through ubitsoft's T-SQL Analyzer and I get the following:
SA0028 : [Deprecated]Function call can be extracted from the WHERE clause...
--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)

January 16, 2014 at 12:06 pm

#1680814
RE: Calculating Duration Using DATETIME Start and End Dates (SQL Spackle)

I can't spend much more time on the discussions that are unfolding until I get home from work tonight but the folks at SQLServerCentral have posted the correction to 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)

January 16, 2014 at 9:00 am

#1680706
RE: Calculating Duration Using DATETIME Start and End Dates (SQL Spackle)

cready27 (1/16/2014)
I'm guessing the @EndDT for the first code example should have been.
@EndDT = '2000-01-02 12:34:56.789'
That gives the same answer as the example.
Correct. I've submitted a correction. ...
--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)

January 16, 2014 at 8:36 am

#1680694
RE: Calculating Duration Using DATETIME Start and End Dates (SQL Spackle)

Mikael Eriksson SE (1/16/2014)
There is one more thing to notice about the first query.
You assign the value "2000-02-01 12:34:56.789" to @EndDT but the query returns "2000-01-02 12:34:56.789".
Either make sure 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)

January 16, 2014 at 8:21 am

#1680686
RE: Calculating Duration Using DATETIME Start and End Dates (SQL Spackle)

Thanks for the feedback folks. I'm on my way to work and will look into your replies when I get home.
--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)

January 16, 2014 at 7:46 am

#1680663
RE: Splitting DBs into multiple files for IO spread

GilaMonster (1/12/2014)
Jeff Moden (1/12/2014)
GilaMonster (1/12/2014)
Please note: 4 year old thread (bumped by a spam post)
Why does that matter to you so much?
So that people who don't check the dates...
--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)

January 14, 2014 at 7:29 am

#1679894
RE: What is the default sa password?

keymoo (1/14/2014)
paul.knibbs (1/14/2014)
Jeff Moden (1/13/2014)
Unless someone changed it, the SA password is the one used when SQL Server was installed.
But you're not asked to specify an SA password during setup...
--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)

January 14, 2014 at 7:22 am

#1679889

Viewing 15 posts - 24,751 through 24,765 (of 59,099 total)