[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MS SQL

Posted on 2012-09-20
7
Medium Priority
?
371 Views
Last Modified: 2012-09-20
I have written a SQL query returning data.  The WHERE clause is expressed:

WHERE PROCESS_DATE > '01-01-2012'

How do I express the WHERE PROCESS_DATE > '01-01-CURRENT YEAR' to avoid having to change the SQL every year?
0
Comment
Question by:garyjgs
7 Comments
 
LVL 5

Expert Comment

by:tlayton
ID: 38417574
Try something like this with dynamic SQL:

@SQL = 'SELECT * FROM TableName WHERE PROCESS_DATE > ''01-01-' + YEAR(GETDATE()) 
EXEC (@SQL)

Open in new window

0
 
LVL 5

Expert Comment

by:tlayton
ID: 38417583
Slight correction:

@SQL = 'SELECT * FROM TableName WHERE PROCESS_DATE > ''01-01-' + YEAR(GETDATE()) + ''
EXEC (@SQL)

Open in new window

0
 
LVL 5

Expert Comment

by:tlayton
ID: 38417600
3rd time lucky:

DECLARE @SQL varchar(1000)
SELECT @SQL = 'SELECT * FROM TableName WHERE PROCESS_DATE > ''01-01-' + CAST(YEAR(GETDATE()) as varchar) + ''''
EXEC @SQL

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Expert Comment

by:LIONKING
ID: 38417651
What SQL are you using? 2008? 2012?
0
 
LVL 6

Expert Comment

by:Peter Kiprop
ID: 38417694
Hi  garyjgs,

Please try the below.

Declare @plyear as varchar(4) 
Declare @mydate as varchar(25)                              
set @plyear =year(getdate())                              
set @mydate= @plyear + '-01-01 00:00:00.000'  
SELECT * FROM TableName WHERE PROCESS_DATE >@mydate

Open in new window


Hope it helps.
0
 
LVL 25

Accepted Solution

by:
lwadwell earned 2000 total points
ID: 38417702
try:
    WHERE PROCESS_DATE > dateadd(yy, datediff(yy, 0, getdate()), 0)
0
 

Author Closing Comment

by:garyjgs
ID: 38417739
Worked well with my scenario.  Thank you.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

872 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