• Steven Willis (1/2/2013)


    The output was for a golf tournament signup schedule and the end-user needed a column to display tee times. These were actual times of course and not durations. The day date was on the page showing the tee times for that day. I'd think this might apply as well to any scheduling situation such as at a doctor's office, etc.

    From the part of you statement I highlighted above it's obvious you still need to know on which day any particular time will be used.

    Time is still bound to date, and it's totally irrelevant if disconnected.

    What you are describing is a presentation issue, and it must be dealt with on UI, not in databse.

    I do not see you are using TIME datatype to solve any other issue than presentation format.

    And DATYTIME is still easier to use here.

    "Date" portion of any datetime value defines if this time to be displayed on the page for the selected date.

    When you display some events for a day you include all date-time values which fall in between of beginning of the day and end of the day.

    When you display events for an afternoon you include all date-time events between midday of the day and end of the day.

    Now, try to do it with separate date and time! You'll have to bring date and time together into a datetime value and work it out from there.

    And the format of the "time" portion displayed on the page is better defined by using CONVERT to string data types (varchar, nvarchar, etc.) rather than to TIME (I'm pretty sure users won't be happy to see on UI all those trailing zeros showed in your examples).

    DECLARE @TeeTime DATETIME

    SET @TeeTime = '14:22'

    SELECT CONVERT(char(8), @TeeTime, 8) AS TeeTime

    --> Output: 14:22:00

    SELECT CONVERT(char(5), @TeeTime, 8) AS TeeTime

    --> Output: 14:22

    Very nice display, much better than from using implicit conversions from TIME data type.

    Same logic applies to inserting a time.

    When setting up a time you have you date selected, and you effectively are setting date-time, not time only.

    Bind date and time parts of the event on the way from UI to database as save it as it should be saved - datetime value.

    You mentioned "doctor's office" as an example. I wonder - how many times did you hear about a doctor's appointment set up for a time without specifying a date?

    Would you be happy to check on doctor's office every day to find if the appointment time you've got is for today actually?

    :hehe:

    _____________
    Code for TallyGenerator