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!
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

813 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

9 Experts available now in Live!

Get 1:1 Help Now