Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-10-25
4
Medium Priority
?
438 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
  • 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 2000 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

971 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