Solved

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

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

0
Comment
Question by:Jimmy_inc
  • 2
4 Comments
 
LVL 18

Expert Comment

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

0
 
LVL 18

Accepted Solution

by:
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

Or

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.
0
 

Author Closing Comment

by:Jimmy_inc
ID: 36578335
Thanks
0
 
LVL 5

Expert Comment

by:AlokJain0412
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
  ]
END
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

856 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