| Author |
 Topic  |
|
|
clintc10
Starting Member
USA
4 Posts |
 Posted - 11/19/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/19/2012 : 14:08:27
|
| Is this in T-SQL or SQL Reporting Services? |
 |
|
|
clintc10
Starting Member
USA
4 Posts |
Posted - 11/19/2012 : 14:18:29
|
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 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/19/2012 : 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
USA
4 Posts |
Posted - 11/19/2012 : 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 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/19/2012 : 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
USA
4 Posts |
Posted - 11/19/2012 : 15:49:18
|
| that worked, thanks! |
|
|
| |
Topic |
|
|
|
SQL Help
