How to use SQL Server EE using CASE to evaluate date of datetime field

Posted on 2011-09-21
Last Modified: 2012-05-12
      CASE WHEN MyDateTime >= '20100101' AND <= '20110101' THEN 'Between 2010 01 01 AND 2011 01 01' END as Period
I realize it is something like:
trunc(MyDateTime) or LEFT(MyDateTime,8)
But not sure of the overall syntax..

Question by:Jimmy_inc
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
LVL 18

Expert Comment

ID: 36577146
CASE WHEN DATEPART(yyyy,MyDateTime) = 2010 THEN 'Between 2010 01 01 AND 2011 01 01' END as Period

LVL 18

Accepted Solution

lludden earned 125 total points
ID: 36577157
If you don't include an else, it will put nulls in

CASE WHEN DATEPART(yyyy,MyDateTime) = 2010 THEN 'Between 2010 01 01 AND 2011 01 01' ELSE 'Another Period' END as Period


CASE WHEN MyDateTime BETWEEN '2010-01-01' AND '2011-01-01' THEN 'Between 2010 01 01 AND 2011 01 01' ELSE 'Another Period' END AS Period

You can add as many WHEN's as you want.

Author Closing Comment

ID: 36578335

Expert Comment

ID: 36578410

See Following is the Case When Syntex

CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  WHEN valueN THEN resultN
    ELSE elseResult

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

630 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