various working with dates issues

[wireless200]

I've got a table with some datetime fields in it.

One field (call it field 1) is of the form mm/dd/yyyy and the other two
(fields 2 and 3) are in the form of hh:mm:ss:xx where xx is hundreths
of a second.

I'm getting the difference between field 2 and 3 using (datediff(ms,
access_time, release_time )/1000/60.). This seems to work fine.

However, in some other cases I'd like to add field 1 to field 2 and
then manipulate the result. This is where it gets weird.

If I do it like this: Convert(varchar 20),record_date+access_time,100),
it adds field 2 ok but subtracts two days. So for example 3/1/05 +
10:30:00 AM = 2/27/05 10:30:00 AM. So it effectively subtracts two
additional days for no apparent reason. If is use
record_date+2+access, then this returns the correct answer.

If I try to use Convert(varchar(20),dateadd(ss,record_date,
access_time),100) sql server complains Argument data type datetime is
invalid for argument 2 of the dateadd function.

Basically I'd just like to know how to add and subtract fields 1
(mm/dd/yyyy format) and 2 (hh:mm:ss:xx format).

As a bonus question, is it possible to get an average time for several
different times? For example the average time between 10:30 and 11:00
would be 10:45.

regards,
-David

[Hugo Kornelis]

On 7 Mar 2005 11:09:35 -0800, wi*********@yahoo.com wrote:

Hi David,

Since you have various datetime related questions, I'll first provide
the URL to Tibor Karaszi's Ultimate guide to datetime. According to my
notes, the URL is http://www.karaszi.com/SQLServer/info_datetime.asp. I
can't open the page right now, so it appears that Tibor's server is
down; let's hope it gets back up and running soon!

I've got a table with some datetime fields in it.

One field (call it field 1) is of the form mm/dd/yyyy and the other two
(fields 2 and 3) are in the form of hh:mm:ss:xx where xx is hundreths
of a second.
This is not correct. If you define columns as datetime, they are stored
in an internal format (consisting of two integers). What you see on your
screen is the result of editing by your client application. Based on
your description, I think you are using Enterprise Manager to check your
data; my advise is not to do that. Use Query Analyzer instead - this
gives the most vanilla view of the data. QA uses one standard format for
all datetime values and doesn't attempt to think for you.

EM does attempt to think for you. This is what cuases your confusion.
You see, each datetime value has both a date and a time portion. If the
time portion is not set when the value is supplied, SQL Server defaults
to midnight. EM will suppress the display of the time part when it is
equal to midnight, thinking that you probably are interested in the date
only. EM could be correct of course, but it could be wrong as well.

A similar thing happens if you supply only the time portion for a
datetime value: SQL Server will default the date to the base date for
datetime calculations: January 1st 1900 (or, in standard YYYYMMDD
notation, 19000101). And EM will attempt to suppress this portion,
showing the time only. Unfortunately, this part of EM is not only trying
to think for you, it is also bugged. You see, EM thinks the base date is
not 19000101, but 18991229. So if a datetime column holds 18991229
12:45, is will show in EM as just 12:45; if a datetime column holds
19000101 12:45 (what SQL Server will use if you supply '12:45' as
input), EM will show both date and time!

I'm getting the difference between field 2 and 3 using (datediff(ms,
access_time, release_time )/1000/60.). This seems to work fine.

However, in some other cases I'd like to add field 1 to field 2 and
then manipulate the result. This is where it gets weird.

If I do it like this: Convert(varchar 20),record_date+access_time,100),
it adds field 2 ok but subtracts two days. So for example 3/1/05 +
10:30:00 AM = 2/27/05 10:30:00 AM. So it effectively subtracts two
additional days for no apparent reason. If is use
record_date+2+access, then this returns the correct answer.
Obviously, you're not only using EM for display, but for data entry as
well. So you get doubly bitten by EM's datetime bug. If you manually
enter only a time in EM, EM will happily add the date that it thinks is
the base date (18991229) before sending it to SQL Server. SQL Server
gets a complete datetime and has no need to add the base date. The
datetime value gets stored at two days before SQL Server's base date.

You'd see this immediately if you use Query Analyzer to query the table,
but EM will hind the incorrect data from view. Until you start using it
in calculations, of course - then you suddenly see a two day difference
in your results!!

You can use various ways to get the correct result. One kludge is to
siimply add two days to the result; you've already found that. Another
kludge is to use COVNERT with styule parameters to convert both values
to character strings, one in the format yyyy-mm-dd, one in the format
hh:mm:ss (or hh:mm:ss.ttt), then concatenate them, together with a
capital T to get the ISO standard yyyy-mm-ddThh:mm:ss.ttt or
yyyy-mm-ddThh:mm:ss format and convert that string back to datetime. But
the only good way to fix this is to make sure that the data in your
table is correct: if you really need the time portion only, make sure
that the date portion is set to the real base date: 19000101.

If I try to use Convert(varchar(20),dateadd(ss,record_date,
access_time),100) sql server complains Argument data type datetime is
invalid for argument 2 of the dateadd function.
Yes, to do this, you would first have to convert access_time to a number
of seconds. But unless you exclude the date part in that conversion,
you'd still be two days off <g>.

Basically I'd just like to know how to add and subtract fields 1
(mm/dd/yyyy format) and 2 (hh:mm:ss:xx format).
Though not documented, using record_date + access_time does work. Just
keep in mind that both date and time portion get added (where adding
dates is in fact adding the number of dates since 19000101).

As a bonus question, is it possible to get an average time for several
different times? For example the average time between 10:30 and 11:00
would be 10:45.

declare @start datetime, @end datetime
set @start = '10:30'
set @end = '11:00'
select dateadd(second, datediff(second, @start, @end) / 2, @start)

Best, Hugo
--

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

[Erland Sommarskog]

(wi*********@yahoo.com) writes:

If I do it like this: Convert(varchar 20),record_date+access_time,100),
it adds field 2 ok but subtracts two days. So for example 3/1/05 +
10:30:00 AM = 2/27/05 10:30:00 AM. So it effectively subtracts two
additional days for no apparent reason. If is use
record_date+2+access, then this returns the correct answer.

SELECT convert(datetime, convert(char(8), field1, 112) + ' ' +
convert(char(12), field2, 118))

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp