Select*
From <table>
where recorddate between '20080501' and '20080531'
That will give me all the data I need but the dates needs to be added manually instead of it calculating by itself what the first and last date is of the previous month...
Sorry, forgot to add. You will run into a problem eventually cos, say you have 2 years worth of records right. If you run it for May, it will pull up records for May for the current year, and May for previous year. You will have to check on the dates year value too.
CREATE FUNCTION [dbo].[ufn_GetFirstDayOfMonth] ( @pInputDate DATETIME )
RETURNS DATETIME
BEGIN
RETURN CAST(CAST(YEAR(@pInputDate) AS VARCHAR(4)) + '/' +
CAST(MONTH(@pInputDate) AS VARCHAR(2)) + '/01' AS DATETIME)
END
GO
CREATE FUNCTION [dbo].[ufn_GetLastDayOfMonth] ( @pInputDate DATETIME )
RETURNS DATETIME
BEGIN
DECLARE @vOutputDate DATETIME
SET @vOutputDate = CAST(YEAR(@pInputDate) AS VARCHAR(4)) + '/' +
CAST(MONTH(@pInputDate) AS VARCHAR(2)) + '/01'
SET @vOutputDate = DATEADD(DD, -1, DATEADD(M, 1, @vOutputDate))
RETURN @vOutputDate
END
GO
SELECT fishpaste
FROM yackityschmackity
WHERE YourDateField BETWEEN [dbo].[ufn_GetFirstDayOfMonth] (DATEADD(M, -1, InputDate)) AND [dbo].[ufn_GetLastDayOfMonth] (DATEADD (M, -1, InputDate))
This is a long way round though. Think you should rather pass the date parameters in with the query, i.e. like in VB
Not to put a damper on your resolve but just remember that when using the "BETWEEN" function it only queries data "between" the two dates and not including.
If memory serves me right it includes the first date but not the last date.
You may want to double check that the results you are presented with are accurate.