Solved

SQL Time Comparisons

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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

733 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