?
Solved

SQL Time Comparisons

Posted on 2007-11-15
2
Medium Priority
?
166 Views
Last Modified: 2010-04-21
I have a query that I need to produce that gives the mealperiod that a particular order was produced in.  (Breakfast, Lunch, Dinner).  

This is so that my report can be exported into Excel and grouped/manipulated/massaged, etc.

However, nothing do seems to be able to get anything other than 'Dinner' as a result.
(possibly irrelevant note:  I don't care about the date, even though SQL forces me to store one... all I care about is the TIME portion of the 'TargetDelivery' field to make a determination of category)

Many thanks in advance.

Jay
----------------------------------------------------------------
Case 
        When CONVERT(DATETIME, OrderEntry.TargetDelivery, 108) <= CONVERT(DATETIME, '10:00:00 AM',8) Then 'Breakfast' 
 
When CONVERT(DATETIME, OrderEntry.TargetDelivery, 108) > CONVERT(DATETIME, '10:00:00 AM',8) AND CONVERT(DATETIME, OrderEntry.TargetDelivery, 8) < CONVERT(DATETIME, '3:00:00 PM',8) Then 'Lunch' 
 
Else 'Dinner' 
End As 'Meal Period', 
 
-------------------------------------------------------------------------

Open in new window

0
Comment
Question by:DataWedge
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 8

Accepted Solution

by:
k_rasuri earned 500 total points
ID: 20291369
your query wont give the proper results as you cannot just convert 10:00am to date. instead use datpart something like this

select case when datepart(hh,getdate())<11 then 'Breakfast'
                    when datepart(hh, getdate()) between 11 and 18 then 'Lunch'
                   else 'Dinner' end
0
 

Author Closing Comment

by:DataWedge
ID: 31409391
This works perfectly.  Thanks.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

762 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