SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

clintc10
Starting Member

USA
4 Posts

Posted - 11/19/2012 :  14:06:10  Show Profile  Reply with Quote
I need an expression to give me a result for month end reports that reads: “From 10/1/2012 to 10/31/2012” (and advances for each month.)

="From " + (DATEADD("mm",-1,DATEADD("mm",DATEDIFF("mm",0,TODAY),0))) + " to " + (DATEADD("ms",-3,DATEADD("mm",0,DATEADD("mm",DATEDIFF("mm",0,TODAY),0))))

but it doesnt work, i get an error.

Can someone help me please.

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/19/2012 :  14:08:27  Show Profile  Reply with Quote
Is this in T-SQL or SQL Reporting Services?
Go to Top of Page

clintc10
Starting Member

USA
4 Posts

Posted - 11/19/2012 :  14:18:29  Show Profile  Reply with Quote
sql server management studio, is that what you are looking for?

i guess t sql

Edited by - clintc10 on 11/19/2012 14:23:46
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/19/2012 :  14:23:19  Show Profile  Reply with Quote
Yes.

For current month:
'From ' 
+ CONVERT(CHAR(10),DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0),101) 
+ ' to ' 
+ CONVERT(CHAR(10),DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,-1),101) 
If you are looking for last month
'From ' 
+ CONVERT(CHAR(10),DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0),101) 
+ ' to ' 
+ CONVERT(CHAR(10),DATEADD(mm,DATEDIFF(mm,0,GETDATE()),-1),101) 
Go to Top of Page

clintc10
Starting Member

USA
4 Posts

Posted - 11/19/2012 :  14:50:24  Show Profile  Reply with Quote
how would i get that to work in sql business intelligence development studio

it works in management studio but when i put it in business intelligence development studio it doesnt work. Thank for your help
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/19/2012 :  15:01:01  Show Profile  Reply with Quote
One of these, depending on whether you want current month or last month

= "From " 
& Format(dateadd("m",datediff("m","1/1/1900",today()),"1/1/1900"),"MM/dd/yyyy")
& " to "
& Format(dateadd("m",datediff("m","1/1/1900",today())+1,"12/31/1899"),"MM/dd/yyyy")


= "From " 
& Format(dateadd("m",datediff("m","1/1/1900",today())-1,"1/1/1900"),"MM/dd/yyyy")
& " to "
& Format(dateadd("m",datediff("m","1/1/1900",today()),"12/31/1899"),"MM/dd/yyyy")
Go to Top of Page

clintc10
Starting Member

USA
4 Posts

Posted - 11/19/2012 :  15:49:18  Show Profile  Reply with Quote
that worked, thanks!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000