[thelist] MSSQL: adding integer to time yields date?

[Canfield, Joel]

The writer in me compelled me to put the truly salient info way down
there after the asterisks, and address other things in order.

> What is the EXACT code you are using when you say "All it returns is
> '12/31/1899'"  Am I to assume it's something like:
> 
> Response.Write datEventEndTime

In an unordered list, it's one list item:

    <li>Time: <%=datStartTime%> to <%=datEndTime%></li>

> So Joel, are you SURE it's a smalldatetime?  I am now lead to 
> believe it's probably a datetime field.  :-)

It's smalldatetime, honest it is ;) I just checked again to be
absolutely sure. 

However, I probably did my best to break it, 'cause originally, it *was*
datetime. When I started having this problem, my reading led me to
believe that smalldatetime would remove certain variables from the
problem so I changed the db design in EM. Probably a mistake, and since
the table is dead simple and Best Beloved needs a little more db
experience, we'll probably rebuild it from scratch just for practice.

> So, the second answer is ALSO yes, there is a better way.  
> Use formatting
> functions (FormatDateTime, for example) to ensure that you 
> are in control of
> how dates are displayed.

Well, in my efforts, no amount of formatting (short of parsing it into
little string bits and concatenating it all in the ugliest code you can
imagine) had any effect whatsoever. I think your comments about MSSQL
throwing surprise NULLs at me is salient.

and Ken wrote:

> So, it appears that if your time addition results in a 
> time that's exactly
> midnight, the 00:00 gets dropped (resulting in a date only).
> But if your time
> addition results in any time other than midnight, the time 
> portion is present.

Yup, I could duplicate that. Problem is, most of my clients are bands,
and most of their gigs end at midnight. So, while in many cases this
would be a minor inconvenience, in this case, it's a major flaw.

*****************************

So, here's the real solution:

*****************************

There *is* a better way. Went home and did some thinking out loud with
Best Beloved, and instead of approaching this as a technical db/script
problem, I picked her brains about user interface stuff. She convinced
me that, for the clients who'd be having her maintain their calendars,
she'd rarely be inputting a start time and a duration; it would
virtually always be start time and end time (which as someone, [Joshua?]
pointed out, would allow DateDiff to calculate an integer for the
length, if I wanted it.)

So, I've been trying to solve the wrong problem. One of the first things
I didn't learn from my dad 30 years ago when he tried to teach me the
principles of quality and production control, is first, make sure you're
solving the right problem. We'll just use two smalldatetime fields,
StartTime and EndTime, which I've already seen will display as I want.
(Datetime includes seconds and partial seconds, resulting in a display
like '8:00:00 PM' whereas smalldatetime  uses only minutes, displaying
'8:00PM' as I want.)

This has been a fascinating and educational thread for me, and I really
appreciate the effort y'all took, Joshua, Ken, Phil, et al.

joel