SQL Date

[SLIP]

Hi,

What I need to do is...

I am building a report in Reporting Services.
Now if the report gets generated today(06/2008) the report must be dated for the (05/2008).

Now I came up with...:

Code: Select all

select (month((GetDate()))-1)

That returns the value 5.

but I need to select the data for the report between the first date and the last date of the previous month.

like so..:

Code: Select all

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...

Please Help?

[SilverBack]

Code: Select all

DATEADD (datepart , number, date )

Wont this do the trick?

[SLIP]

[quote="SilverBack"]

Code: Select all

DATEADD (datepart , number, date )

Im still a noob with this.....so can you tell me what values to put where?

[SilverBack]

Oh, ok. Sorry. Erm...hmmm, lets see...

EDIT: Better solution below

[SilverBack]

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.

This is more effective:

Code: Select all

SELECT * FROM <table>
WHERE MONTH(recorddate) = MONTH(DATEADD(m, -1, getdate()))
AND YEAR(recorddate) = YEAR(getdate())

[GrimStoner]

From Google...

Code: Select all

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

and ...

Code: Select all

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

Then ...

Code: Select all

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

Code: Select all

SELECT fishpaste 
FROM yackityschmackity 
WHERE YourDateField BETWEEN @DateFrom AND @DateTo

and then do the date calculations in VB.

[GrimStoner]

Silverback's is easier tho... Just do a check on the year as well.

Edit : Rofl... lots easier...

[SLIP]

Thanks works 100%

[SLIP]

Thanks Silver...

[SilverBack]

Sweet

Looks like Grims will work too...also a nice way to do it. Will help with getting to know how things function in SQL etc

Did you see the YEAR option added in? I updated my first answer...and added the second better way

[Stevo]

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.

[Hex_Rated]

Random tip: Converting to UTC time (seconds past midnight 1970) often helps when your messing around with dates.

I hate working with dates and times in databases.

[SilverBack]

Random tip: Converting to UTC time (seconds past midnight 1970) often helps when your messing around with dates.

I hate working with dates and times in databases.

Lets not even go there!! I have had numerous headaches with dates....hours spent on it....hate workign with dates too

[GrimStoner]

MySQL sucks with dates.