Solved

take year from date and convert to a predefined month and date.

Posted on 2013-10-25
4
431 Views
Last Modified: 2013-10-28
is there a better syntax to do the below: a date is given.. it can be any date.. we have to ignore the date and month of that input, but just the year.. then we need the result set of predined April 12th to 16th of that year.

DECLARE @YEAR CHAR(4)
DECLARE @DateBegin CHAR(10)
DECLARE @DateEnd CHAR(10)
select @YEAR = CAST(YEAR('2011-11-19') as CHAR(4)) /*this could be any date */
select @DateBegin = @YEAR+'-04-12'
select @DateEnd = @YEAR+'-04-16'
select  'BETWEEN '''+ @DateBegin+''' AND ''' + @DateEnd +''''

then i have to use the above inside a query in the WHERE condition.
0
Comment
Question by:25112
[X]
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
4 Comments
 
LVL 15

Expert Comment

by:Ess Kay
ID: 39601140
not sure what you are trying to do there, but if you are going to hardcode it, then hardcode the whole thing
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 39601143
You've pretty much nailed it.  Give this a whirl..
Declare @dt date = '2012-05-31'

SELECT CAST(CAST(YEAR(@dt) as char(4)) + '-04-12' as date) as start_date
SELECT CAST(CAST(YEAR(@dt) as char(4)) + '-04-16' as date) as end_date

Open in new window

As an aside, keep in mind that using two dates in BETWEEN means that you're including both of them, and that those two days have a time component of midnight, so it wouldn't capture a value of say 2013-04-16 12:00:00.   SQL expert PortletPaul wrote an article on this.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39601152
... so, if you want to capture all 2013-04-16 values regardless of time, then instead of

WHERE SomeValue BETWEEN '2013-04-12' AND '2013-04-16'
--  it'll be
WHERE SomeValue >= '2013-04-12' AND SomeValue < '2013-04-17'
0
 
LVL 5

Author Comment

by:25112
ID: 39605640
good solution- thx.
0

Featured Post

RHCE - Red Hat OpenStack Prep Course

This course will provide in-depth training so that students who currently hold the EX200 & EX210 certifications can sit for the EX310 exam. Students will learn how to deploy & manage a full Red Hat environment with Ceph block storage, & integrate Ceph into other OpenStack service

Question has a verified solution.

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

In this article I will describe the Detach & Attach 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.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
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…

622 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