Solved

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

Posted on 2013-10-25
4
424 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 65

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 65

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

867 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now