Return modified datetime data in Select...Case

Posted on 2011-10-03
Last Modified: 2012-05-12
I have a stored procedure that returns appointment data into a form.  Everything was working fine but I've been tasked with the following:

1. If the appointment is flagged as appAllDay = 1, the appointment start time (appDate) needs to display with a different time.  The physical data isn't touched or changed.

For example, 2010-02-05 08:00:00.000 needs to display as 2010-02-05 11:00:00.000 in my appTime column shown below.

The appDate field is a datetime column so I'm not sure if this is even possible.  Any assistance would be appreciated.

SELECT dbo.dbAppointments.appID,
(case appAllDay when 0 then appDate else
end) as appTime
WHERE dbo.dbAppointments.feeusrid = 7459
Question by:smsstech
    LVL 7

    Accepted Solution

    Am I missing something, or would this work?

    SELECT dbo.dbAppointments.appID,
    (case appAllDay when 0 then appDate else
    dateadd(hh, 11, convert(datetime, convert(varchar(8), appDate, 112), 112))
    end) as appTime
    WHERE dbo.dbAppointments.feeusrid = 7459

    convert it from a date to a varchar, back to a date (to remove the time portion), then add 11 hours to it.  
    LVL 59

    Assisted Solution

    by:Kevin Cross
    I agree, that approach should work, i.e., grabbing date at midnight and adding hours/minutes back to get to the time of day you want. As an aside, if you are using SQL Server 2008, there are new data types for DATE and TIME, separately; therefore, you may want to see if those are a better fit for your design. A NULL TIME for example might symbolize all day or at least you can always make time whatever you want based on appAllDay.


    Author Closing Comment

    Thanks.  Fantastic work.

    I don't deal with datetime in SQL very often so manipulation was new to me.  I appreciate the sample and the SQL Server 08 notes.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now