Solved

SQL Time Comparisons

Posted on 2007-11-15
2
162 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 125 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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

729 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