Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Help

Author  Topic 

clintc10
Starting Member

4 Posts

Posted - 2012-11-19 : 14:06:10
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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-19 : 14:08:27
Is this in T-SQL or SQL Reporting Services?
Go to Top of Page

clintc10
Starting Member

4 Posts

Posted - 2012-11-19 : 14:18:29
sql server management studio, is that what you are looking for?

i guess t sql
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-19 : 14:23:19
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

4 Posts

Posted - 2012-11-19 : 14:50:24
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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-19 : 15:01:01
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

4 Posts

Posted - 2012-11-19 : 15:49:18
that worked, thanks!
Go to Top of Page
   

- Advertisement -