TSQL Convert time differences to string

Hi

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?

Thanks,
kplan
keplanAsked:
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'
END  

Open in new window

0
 
teebonProduct ManagerCommented:
By "difference" are you having two datetime values and you getting the difference between them?
0
 
keplanAuthor Commented:
It is same day in 24 hr
0
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?
0
 
teebonProduct ManagerCommented:
if you only have one datetime and the time is 1000 am, it should be "morning" OR "all day"?
0
 
keplanAuthor Commented:
Yes, I have one date time, so based on that I need to decide "morning", or "all day" or "evening" etc
0
 
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.
0
 
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?

thanks
manjula
0
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.