TSQL Convert time differences to string

Posted on 2012-09-06
Last Modified: 2012-09-10

I've a requirments as follow, base on the time range, i need to convert the difference to string on to a table field. my business logic is below:

830 to 12 30 = morning
1.30 to 5 = afternoon
930 to 5pm is all day
Start after 5pm is evening

Can anybody help on this please?

Question by:keplan
    LVL 9

    Expert Comment

    By "difference" are you having two datetime values and you getting the difference between them?

    Author Comment

    It is same day in 24 hr
    LVL 40

    Expert Comment

    Do you have one datetime column and want to display "morning","afternoon","all day" or "evening" based on time portion?
    LVL 9

    Expert Comment

    if you only have one datetime and the time is 1000 am, it should be "morning" OR "all day"?

    Author Comment

    Yes, I have one date time, so based on that I need to decide "morning", or "all day" or "evening" etc
    LVL 9

    Expert Comment

    You haven't answered my question:

    Date : 1 September 2012 10:00 am

    Is the above "morning" OR "all day"?

    I'm asking this because the "all day" category conflicts with "morning" and "afternoon". You have to clarify this requirement first before anything can proceed.

    Author Comment

    Dear Teebon,

    if any time period  falls into 8.30 to 12 30 = morning
    if any time period falls into 1.30 to 5 = afternoon
    if any time period falls into 930 to 5pm is all day
    if any time after 5pm is evening

    eg:  from datetime 2010-05-28 08:35:00.000
           and to datetime 2010-05-28 12:25:00.000

     I need to get the answer 'Morning' likewise

    from datetime 9.30 To datetime 5pm, I should get the answer 'All day'

    this is single day circle,

    did I answer your query very well?

    LVL 9

    Accepted Solution

    Hi Manjula,

    In that case, you have TWO datetime field instead of ONE.
    It is clear now. Please find the code as below, you just need to change the value @fromTime and @toTime accordingly:

    declare @fromTime datetime
    declare @toTime datetime
    declare @fromMinutes int
    declare @toMinutes int
    SET @fromTime = '2010-05-28 09:30:00.000'
    SET @toTime = '2010-05-28 17:00:00.000'
    select @fromMinutes = PARSENAME(REPLACE(CONVERT(VARCHAR(8),@fromTime,108),':','.'),2) + PARSENAME(REPLACE(CONVERT(VARCHAR(8),@fromTime,108) ,':','.'),3) * 60
    select @toMinutes = PARSENAME(REPLACE(CONVERT(VARCHAR(8),@toTime,108) ,':','.'),2) + PARSENAME(REPLACE(CONVERT(VARCHAR(8),@toTime,108) ,':','.'),3) * 60
    SELECT CASE  WHEN  @fromMinutes >= 510 AND @toMinutes <= 750 THEN 'morning'  
    WHEN  @fromMinutes >= 810 AND @toMinutes <= 1020 THEN 'afternoon'
    WHEN  @fromMinutes >= 570 AND @toMinutes <= 1020 THEN 'all day'
    WHEN  @fromMinutes >= 1020 THEN 'evening'

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Many times while working on a computer regardless of any Operating System, lag and crashes seem to creep in, hindering your working speed. Sometimes, it can also cause your work to be lost unexpectedly and as a result, you are unable to meet your de…
    Log files are useful in diagnosing and repairing problems.  This is a list of common log files and their standard locations that I've compiled.   While this is not exhaustive, it is a pretty good list that I've found to be useful.  I may update it f…
    In this video, we discuss why the need for additional vertical screen space has become more important in recent years, namely, due to the transition in the marketplace of 4x3 computer screens to 16x9 and 16x10 screens (so-called widescreen format). …
    With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

    794 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

    18 Experts available now in Live!

    Get 1:1 Help Now