[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2014-08-06
7
Medium Priority
?
1,307 Views
Last Modified: 2014-08-06
Hello:

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?

Thanks!

TBSupport
0
Comment
Question by:TBSupport
  • 3
  • 3
7 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 40244655
SELECT cast('01/01/'+cast(datepart(year,getdate()) as sysname) as date);


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

  SELECT
      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))
0
 
LVL 1

Author Comment

by:TBSupport
ID: 40244676
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?

TBSupport
0
 
LVL 1

Author Comment

by:TBSupport
ID: 40244680
Me, again.  

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

TBSupport
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 40244692
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.
0
 
LVL 40

Accepted Solution

by:
lcohan earned 2000 total points
ID: 40244694
Yes, you should be able to use    
   SELECT cast('01/01/'+cast(datepart(year,getdate()) as sysname) as date);
in @BEGDATE
0
 
LVL 1

Author Comment

by:TBSupport
ID: 40244702
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.

TBSupport
0
 
LVL 40

Expert Comment

by:lcohan
ID: 40244717
"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.
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

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

A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

834 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