TSQL Convert time differences to string


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?

Who is Participating?
teebonProduct ManagerCommented:
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

teebonProduct ManagerCommented:
By "difference" are you having two datetime values and you getting the difference between them?
keplanAuthor Commented:
It is same day in 24 hr
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

SharathData EngineerCommented:
Do you have one datetime column and want to display "morning","afternoon","all day" or "evening" based on time portion?
teebonProduct ManagerCommented:
if you only have one datetime and the time is 1000 am, it should be "morning" OR "all day"?
keplanAuthor Commented:
Yes, I have one date time, so based on that I need to decide "morning", or "all day" or "evening" etc
teebonProduct ManagerCommented:
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.
keplanAuthor Commented:
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?

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.