Case statement based on hour in timestamp

Posted on 2008-06-19
Last Modified: 2010-04-21
I have a timestamp with date and time in it. I want to assign a value to a record which depends on the time of day but the date is irrelevant. I need to write a case statement something like this:

case when (start time between 10 pm and 6 am) and (end time - start time is > 60) then do this
when end time - start time > 20 then do this
else do this end

I don't know the syntax to evaluate a timestamp which only looks at the hour.
Question by:Spur
  • 3
  • 3
LVL 18

Accepted Solution

jcoehoorn earned 125 total points
ID: 21822186
CASE WHEN (DATEPART(hh, StartTime) < 6 OR DATEPART(hh, StartTime) > 22) AND DATEDIFF(mi, EndDate, StartDate) > 60 THEN
-- first condition
ELSE WHEN DATEDIFF(mi, EndDate, StartDate) > 20 THEN
-- 2nd condition
LVL 18

Expert Comment

ID: 21822201
Oops, I was thinking minutes for the 60 and 20 conditions.  Change that from 'mi' to 'hh' to use hours instead.  Though perhaps you meant for that to be minutes...

Author Comment

ID: 21822220
Giving it a try now, thanks icoehoorn!
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

LVL 18

Expert Comment

ID: 21822279
Just noticed my enddate and startdate may be backwards also.

Author Closing Comment

ID: 31468764
Thanks, worked great!

Author Comment

ID: 21822647
That part is easy, it is the datepart function I had never used or known about :) Works like a charm, Thanks jcoehoorn

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

911 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now