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? |
|
|
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 |
|
|
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) |
|
|
clintc10
Starting Member
4 Posts |
Posted - 2012-11-19 : 14:50:24
|
how would i get that to work in sql business intelligence development studioit works in management studio but when i put it in business intelligence development studio it doesnt work. Thank for your help |
|
|
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") |
|
|
clintc10
Starting Member
4 Posts |
Posted - 2012-11-19 : 15:49:18
|
that worked, thanks! |
|
|
|
|
|