Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 497
  • Last Modified:

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
0
keplan
Asked:
keplan
  • 4
  • 3
1 Solution
 
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
 
SharathData EngineerCommented:
Do you have one datetime column and want to display "morning","afternoon","all day" or "evening" based on time portion?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now