T-SQL: January 1 of Whatever Year We Are "In"


I have a T-SQL query in a SQL Reporting Services report that contains two user-defined parameters:  @BEGDATE and @ENDDATE.  This beginning and ending date range represents the "check date" range and is chosen by the end user, upon running the report.

I have just been informed that I need to "throw out" @BEGDATE and replace it with January 1 of the year that we are in.  Now, at the time of this posting, the year of course is 2014.  But, for next year and each succeeding year thereafter, we will be in the year 2015, 2016, etc.

What T-SQL syntax do I use, then, that will pull January 1 of the current year that we are in?


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
SELECT cast('01/01/'+cast(datepart(year,getdate()) as sysname) as date);

--Ok here is it exactly as requested "January 1 2014":

      DATENAME(month, cast('01/01/'+cast(datepart(year,getdate()) as sysname) as date))
      ,DATENAME(day, cast('01/01/'+cast(datepart(year,getdate()) as sysname) as date))
          ,DATENAME(year, cast('01/01/'+cast(datepart(year,getdate()) as sysname) as date))
TBSupportAuthor Commented:
Hi lcohan:

Thank you, for the quick response!

I need to substitute @BEGDATE.  So, you're saying that I can put exactly what you typed into the following phrase while replacing @BEGDATE:

checkdate between @BEGDATE and @ENDDATE

Is that correct?

TBSupportAuthor Commented:
Me, again.  

Or, do I take your formula and create a parameter with it?

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Kyle AbrahamsSenior .Net DeveloperCommented:
declare @begDate dateTime
set @begDate = cast('01/01/'+cast(datepart(year,getdate()) as sysname) as datetime)

you can then use that like you would a parameter.
lcohanDatabase AnalystCommented:
Yes, you should be able to use    
   SELECT cast('01/01/'+cast(datepart(year,getdate()) as sysname) as date);

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TBSupportAuthor Commented:
Now, if I use this as a parameter, the end user is not going to get "prompted" for this field.   Right?

If so, then I need to know how to create the parameter without the end user being prompted.

lcohanDatabase AnalystCommented:
"Now, if I use this as a parameter, the end user is not going to get "prompted" for this field.   Right?"

It will be still there but prepopulated with the value returned by the select so they would only need to choose the appropriate @ENDDATE.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

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.