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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics