Time only values

21 Oct 2005 9:53 PM

MurrayTh

I have a table with time values in one field. The field is datetime data
type with no date entered. During some processing, I want to offset the time
in the schedule by a number of minutes, but keep the date value part "empty",
even if the offset causes the time to cross a date boundary.

When trying to offset minutes and strip of the date portion, however, I am
running into troubles.

If I enter a time-only value into the table directly, it looks like the
default date being used for time-only entries is "1899-12-31" (ie entering
6:00 AM displays as "6:00 AM" in the table view, but "1899-12-31
06:00:00.000" in a query analyzer.

After processing the time and offset using this expression:
DateAdd(n,
    DatePart(n,
       DateAdd(n,@lOffset,[pTime])),0)
+DateAdd(hh,
    DatePart(hh,
       DateAdd(n,@lOffset,[pTime])),0)
....the date part is not "1899-12-31", but "1900-01-01". When inserted into
the table, is also now displays the date portion. So instead of adding 30
minutes, it will add a day and 30 minutes...

Do I have something funny going on with the default date (which I thought
was 1900-01-01), or is there another way I should be calculating the adjusted
time value?

When trying to offset minutes and strip of the date portion, however, I am
running into troubles.

I can do this using Date

21 Oct 2005 10:09 PM

Jerry Spivey

Murray,

Might try (as example to work with):

CREATE TABLE #TIME
(TID INT NOT NULL,
TTIME DATETIME NOT NULL)

INSERT #TIME
VALUES(1,GETDATE())

SELECT CONVERT(VARCHAR(8),TTIME,8) AS 'TIME ENTERED',
CONVERT(VARCHAR(8),DATEADD(MI,30,TTIME),8) AS 'TIME ENTERED + 30 MINUTES'
FROM #TIME

--DROP TABLE #TIME

HTH

Jerry
Show quote

"MurrayTh" <Murra***@discussions.microsoft.com> wrote in message
news:550B2921-49DE-41C5-AF1D-68FC0D0DFFEC@microsoft.com...
>I have a table with time values in one field. The field is datetime data
> type with no date entered. During some processing, I want to offset the
> time
> in the schedule by a number of minutes, but keep the date value part
> "empty",
> even if the offset causes the time to cross a date boundary.
>
> When trying to offset minutes and strip of the date portion, however, I am
> running into troubles.
>
> If I enter a time-only value into the table directly, it looks like the
> default date being used for time-only entries is "1899-12-31" (ie entering
> 6:00 AM displays as "6:00 AM" in the table view, but "1899-12-31
> 06:00:00.000" in a query analyzer.
>
> After processing the time and offset using this expression:
> DateAdd(n,
>    DatePart(n,
>       DateAdd(n,@lOffset,[pTime])),0)
> +DateAdd(hh,
>    DatePart(hh,
>       DateAdd(n,@lOffset,[pTime])),0)
> ...the date part is not "1899-12-31", but "1900-01-01". When inserted
> into
> the table, is also now displays the date portion. So instead of adding 30
> minutes, it will add a day and 30 minutes...
>
> Do I have something funny going on with the default date (which I thought
> was 1900-01-01), or is there another way I should be calculating the
> adjusted
> time value?
>
> When trying to offset minutes and strip of the date portion, however, I am
> running into troubles.
>
> I can do this using Date

21 Oct 2005 10:51 PM

MurrayTh

Thanks for the idea, Jerry. I'm still having the same problem, though.

I used your example (which I should have used first, I guess):
CONVERT(VARCHAR(8),DATEADD(mi,30,[pTime]),8)

I also used yet another attempt just adding it based on a text value: DateName(hh,DateAdd(n,@lOffset,[pTime]))+':'+Right('00'+DateName(mi,DateAdd(mi,@lOffset,[pTime])),2)

In both cases, the date portion still has non "empty" amount: '1900-01-01',
instead of the time value alone (with the default date '1899-12-31').

22 Oct 2005 1:47 PM

Aaron Bertrand [SQL Server MVP]

> In both cases, the date portion still has non "empty" amount:
> '1900-01-01',
> instead of the time value alone (with the default date '1899-12-31').

I agree wholeheartedly with Hugo. The first step is to stop using
Enterprise Manager to enter data.

Besides, if you only care about the time, and since the date part is never
"empty" anyway, who cares whether the date is 1900-01-01 or 1899-12-31?

21 Oct 2005 10:42 PM

Hugo Kornelis

On Fri, 21 Oct 2005 14:53:01 -0700, MurrayTh wrote:

>If I enter a time-only value into the table directly, it looks like the
>default date being used for time-only entries is "1899-12-31" (ie entering
>6:00 AM displays as "6:00 AM" in the table view, but "1899-12-31
>06:00:00.000" in a query analyzer.

Hi MurrayTh,

I assume that you're using Enterprise Manager to "enter a time-only
value into the table directly"? If so, then don't. The team of
developers that worked on EM apparently never took the trouble to
communicate with the team that did the actual server code. They got the
base date wrong, and either MS never noticed before shipping, or they
didn't see fit to correct the error.

>Do I have something funny going on with the default date (which I thought
>was 1900-01-01), or is there another way I should be calculating the adjusted
>time value?

The base date is indeed 1900-01-01. Enterprise Manager has it wrong.
Don't use it to enter datetime values. In fact, better don't use it at
all for anything not related to *managing* databases (ie backing up,
restoring, scheduling maintenance and other jobs, setting up
replication, etc). For development, QA is the better choice.

Best, Hugo

--

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