Expression to retreive first day of last month
hi experts,
can any 1 let me know to produce the below sql as expression
SELECT
CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(dateadd(mm,-1,getdate()))-1),
dateadd(mm,-1,getdate())),101)
April 18th, 2011 6:22pm
This should work
=DATEADD(
"d",-(DAY(dateadd("M",-1,Globals!ExecutionTime))-1),
dateadd("M",-1,Globals!ExecutionTime))
though this returns the current time also which you might want to strip out
Free Windows Admin Tool Kit Click here and download it now
April 18th, 2011 6:33pm
Sorry i dont know why it added that on two lines...
=DATEADD("d",-(DAY(dateadd("M",-1,Globals!ExecutionTime))-1),
dateadd("M",-1,Globals!ExecutionTime))
April 18th, 2011 6:33pm
And to just have date without time
=FormatDateTime((DATEADD("
d",-(DAY(dateadd("M",-1,Globals!ExecutionTime))-1),
dateadd("M",-1,Globals!ExecutionTime))), DateFormat.ShortDate)
Free Windows Admin Tool Kit Click here and download it now
April 18th, 2011 6:35pm
=DateAdd("m", -1, DateSerial(Year(Now()), Month(Now()), 1))
-More Date expressions here:(My Blog)
April 18th, 2011 7:03pm
Hi Vishal,
Create a dataset and paste the query below
SELECT
CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(dateadd(mm,-1,getdate()))-1),
dateadd(mm,-1,getdate())),101)
AS [First Day of Last Month]
when you execute it , it will create a column called
[First Day of Last Month].
You can drag and drop that column as expression.
I hope this will solve your issue. All the best.Dasari
Free Windows Admin Tool Kit Click here and download it now
April 18th, 2011 7:34pm
=DateAdd("m", -1, DateSerial(Year(Now()), Month(Now()), 1))
-More Date expressions here:(My Blog)
April 19th, 2011 2:03am