• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 35
  • Last Modified:

MONTHLY VARIABLE

I AM WRITING A QUERY THAT WILL NEED TO IDENTIFY VALUES BASED ON THE MONTH.  I HAVE THE BELOW, HOWEVER IS THERE A BETTER WAY TO IDENTIFY THE MONTHLY VALUES

,CASE when OffDte>='2014-02-01' AND OffDte<='2014-02-28' THEN 1
                                ELSE 0 END AS FRESH
0
Thomask23
Asked:
Thomask23
  • 2
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Dude ... all caps?

For starters, make the below change in bold, as <='2014-02-28' will exclude any values of 02-28 after the beginning midnight.  See PortletPaul's article Beware of Between for more info

, CASE when OffDte >= '2014-02-01' AND OffDte  < '2014-03-01' THEN 1 ELSE 0 END AS FRESH

Also, give us some more details on what you mean by 'IDENTIFY THE MONTHLY VALUES', as that could mean a number of different things
0
 
Mark ElySenior Coldfusion DeveloperCommented:
Use DatePart

DATEPART ( datepart , date )

For month use mm or m

Example
DATEPART (m, '2015-04-24')

Will return 4 for April
0
 
Mark ElySenior Coldfusion DeveloperCommented:
0
 
ste5anSenior DeveloperCommented:
For calculating the month as a column use date part. Or even simpley MONTH(OffDte).

E.g. CASE WHEN MONTH(OffDte) = 2 THEN 1 ELSE 0 END AS FRESH.

Or use IIF with SQL Server 2012+.

When you need to filter on that value using  OffDte >= '2014-02-01' AND OffDte  < '2014-03-01' as predicate is better, cause then indices could be used.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now