SQL Server Programming - time problem

Author

time problem

Jose G. de Jesus Jr MCP, MCDBA

2005-12-16, 7:59 am

hi all,

i have a table with two fields

name, date_hired
a , 2004-04-16 10:02:31.000
B , 2004-04-18 20:02:31.000

i want a select statement that returns

a , 2004-04-16 00:00:00
B , 2004-04-18 00:00:00

--
thanks,

------------------------------------
Jose de Jesus Jr. Mcp,Mcdba
MCP #2324787

Stijn Verrept

2005-12-16, 7:59 am

Jose G. de Jesus Jr MCP, MCDBA wrote:

> i want a select statement that returns
>
> a , 2004-04-16 00:00:00
> B , 2004-04-18 00:00:00

CAST(CONVERT(char(8), date_hired, 112) AS DATETIME)

--

HTH,

Stijn Verrept.

Rogas69

2005-12-16, 7:59 am

so all you want is to get the midnight of the day? this is one of ways:

select name, convert(varchar(19), convert(datetime, convert(varchar(10),
date_hired, 120), 120), 120) date_hired
from theTable

no offence, but mcdba you put aside your name looks weird in this context.

peter

"Jose G. de Jesus Jr MCP, MCDBA" <Email me> wrote in message
news:CE78D972-A0D2-4281-A0F5-B619852BF4FD@microsoft.com...
> hi all,
>
> i have a table with two fields
>
> name, date_hired
> a , 2004-04-16 10:02:31.000
> B , 2004-04-18 20:02:31.000
>
> i want a select statement that returns
>
> a , 2004-04-16 00:00:00
> B , 2004-04-18 00:00:00
>
>
>
>
>
>
> --
> thanks,
>
> ------------------------------------
> Jose de Jesus Jr. Mcp,Mcdba
> MCP #2324787

Jose G. de Jesus Jr MCP, MCDBA

2005-12-16, 7:59 am

thanks
--
thanks,

------------------------------------
Jose de Jesus Jr. Mcp,Mcdba
MCP #2324787

"Stijn Verrept" wrote:

> Jose G. de Jesus Jr MCP, MCDBA wrote:
>
>
> CAST(CONVERT(char(8), date_hired, 112) AS DATETIME)
>
> --
>
> HTH,
>
> Stijn Verrept.
>

Jose G. de Jesus Jr MCP, MCDBA

2005-12-16, 7:59 am

quite busy here.
--
thanks,

------------------------------------
Jose de Jesus Jr. Mcp,Mcdba
MCP #2324787

"Rogas69" wrote:

> so all you want is to get the midnight of the day? this is one of ways:
>
> select name, convert(varchar(19), convert(datetime, convert(varchar(10),
> date_hired, 120), 120), 120) date_hired
> from theTable
>
> no offence, but mcdba you put aside your name looks weird in this context.
>
> peter
>
>
> "Jose G. de Jesus Jr MCP, MCDBA" <Email me> wrote in message
> news:CE78D972-A0D2-4281-A0F5-B619852BF4FD@microsoft.com...
>
>
>

Colin Dawson

2005-12-16, 9:59 pm

Using cast and convert does work, but it's slow as sql needs to change the
datatype twice.
using this...

dateadd(day, datediff(day, 0, getdate()), 0)

instead means that SQL only using a floating point number. It may seem a
little weird at first glance, but it does work. The advantage is that SQL
does not need to convert the date to a different format. As a result of not
needing to convert to a different datatype it means that SQL does not need
to perform any logical reads/writes to do the conversion. Also to make
things even better the entire routine is computed on the processor alone.
This has the net result of using no memory resources, and completely in what
I can only calculate as about 40 times faster than the cast/convert method.

--
Regards

Colin Dawson
www.cjdawson.com

p.s. I didn't come up with this idea. I saw it on this newsgroup about a
year or so ago.

"Stijn Verrept" <stjin@entrysoft.com> wrote in message
news:3aGdnRQ4WMNqKT_eRVny1g@scarlet.biz...
> Jose G. de Jesus Jr MCP, MCDBA wrote:
>
>
> CAST(CONVERT(char(8), date_hired, 112) AS DATETIME)
>
> --
>
> HTH,
>
> Stijn Verrept.

Stijn Verrept

2005-12-17, 7:57 am

Colin Dawson wrote:

> Using cast and convert does work, but it's slow as sql needs to
> change the datatype twice. using this...
>
> dateadd(day, datediff(day, 0, getdate()), 0)
>
>
>
> instead means that SQL only using a floating point number. It may
> seem a little weird at first glance, but it does work. The advantage
> is that SQL does not need to convert the date to a different format.
> As a result of not needing to convert to a different datatype it
> means that SQL does not need to perform any logical reads/writes to
> do the conversion. Also to make things even better the entire
> routine is computed on the processor alone. This has the net result
> of using no memory resources, and completely in what I can only
> calculate as about 40 times faster than the cast/convert method.

How sure are you about this? Have you tested it?

I tried it out on a table with 5000 records and when you look at the
execution plan you'll see they are both 50%.

--

Kind regards,

Stijn Verrept.

Hugo Kornelis

2005-12-17, 7:04 pm

On Sat, 17 Dec 2005 02:41:46 GMT, Colin Dawson wrote:

>Using cast and convert does work, but it's slow as sql needs to change the
>datatype twice.
>using this...
>
>dateadd(day, datediff(day, 0, getdate()), 0)
>
>
>
>instead means that SQL only using a floating point number. It may seem a
>little weird at first glance, but it does work. The advantage is that SQL
>does not need to convert the date to a different format. As a result of not
>needing to convert to a different datatype it means that SQL does not need
>to perform any logical reads/writes to do the conversion. Also to make
>things even better the entire routine is computed on the processor alone.
>This has the net result of using no memory resources, and completely in what
>I can only calculate as about 40 times faster than the cast/convert method.

Hi Colin,

You are correct that this method is faster. But unfortunately, you are
incorrect on most of your other statements.

I have no inside knowledge of how your dateadd/datediff version is
evaluated, but I would be very surprissed if it used floating point
arithmetic. Since datetimes are stored as two integers, one for the date
part and one for the time part, discarding the second integer and doing
a substraction for the datediff or an addition for the dateadd would
make lots more sense.

Both version need data conversion. The data conversions from datetime to
string and back to datetime in Stijn's version are obvious; your version
has two implicit date conversions from integer (0) to datetime.

Neither Stijn's version nor your version has to do any logical or
physical I/O. Reads are only required when you access tables; writes are
only required when you perform UPDATE, INSERT or DELETE statements.

Both versions are computed entirely on the processor, but both do need
some memory, of course, to hold input, output and intermediate results
of the internal procedure to calculate the results.

Finally, the speed difference is nowhere near the "40 times faster" that
you claim. I tested this some months ago. I didn't keep the tests, but
it was easy enough to re-code and re-execute it. Here's the test script
I used, followed by the results on my machine. As you see, the test
consists of a loop (executed a million times). Inside the loop, the date
calculation is executed 5 times (I used 5, not 1, to minimize the effect
of the SET @Count = @Count + 1 and the comparison of @Coount against
100000 that are executed on each iteration of the loop).

-- Start with clean memory
CHECKPOINT
DBCC FREEPROCCACHE WITH NO_INFOMSGS
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
go
-- Test version one (dateadd/datediff)
DECLARE @Start datetime
DECLARE @End datetime
DECLARE @Result datetime
DECLARE @Cnt int
SET @Cnt = 1
SET @Start = CURRENT_TIMESTAMP
WHILE @Cnt < 1000000
BEGIN
SET @Result = DATEADD(day, DATEDIFF(day, 0, @Start), 0)
SET @Result = DATEADD(day, DATEDIFF(day, 0, @Start), 0)
SET @Result = DATEADD(day, DATEDIFF(day, 0, @Start), 0)
SET @Result = DATEADD(day, DATEDIFF(day, 0, @Start), 0)
SET @Result = DATEADD(day, DATEDIFF(day, 0, @Start), 0)
SET @Cnt = @Cnt + 1
END
SET @End = CURRENT_TIMESTAMP
SELECT @Result AS Result,
DATEDIFF(ms, @Start, @End) AS "Elapsed time"
go
-- Start with clean memory
CHECKPOINT
DBCC FREEPROCCACHE WITH NO_INFOMSGS
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
go
-- Test version two (string conversion)
DECLARE @Start datetime
DECLARE @End datetime
DECLARE @Result datetime
DECLARE @Cnt int
SET @Cnt = 1
SET @Start = CURRENT_TIMESTAMP
WHILE @Cnt < 1000000
BEGIN
SET @Result = CAST(CONVERT(char(8), @Start, 112) AS DATETIME)
SET @Result = CAST(CONVERT(char(8), @Start, 112) AS DATETIME)
SET @Result = CAST(CONVERT(char(8), @Start, 112) AS DATETIME)
SET @Result = CAST(CONVERT(char(8), @Start, 112) AS DATETIME)
SET @Result = CAST(CONVERT(char(8), @Start, 112) AS DATETIME)
SET @Cnt = @Cnt + 1
END
SET @End = CURRENT_TIMESTAMP
SELECT @Result AS Result,
DATEDIFF(ms, @Start, @End) AS "Elapsed time"
go

Results:

Result Elapsed time
------------------------------------------------------ ------------
2005-12-17 00:00:00.000 15906

Result Elapsed time
------------------------------------------------------ ------------
2005-12-17 00:00:00.000 61156

The difference between 15.906 seconds vs 61.156 seconds is significant,
but nowhere near "40 times faster". Four times faster is closer.

And finally - a speed difference that is only notable when you execute
the formula five milion times is not exactly a reason to go back and
change existing code. After all, for a single executioon of the formula,
the elapsed time is 3.18 microsecond vs 12.23 microseconds. In actual
code, the time needed to fetch rows is so much more that nobody will
ever notice any speed difference between the two in actual code.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Sponsored Links