Last day of previous month

Hello,

[SQL2000]

CREATE TABLE [dbo].[tbl_FGSIZE]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Rec_Create_Date] [smalldatetime] NOT NULL,
[SizeMB] [numeric](19, 2) NULL,
[UsedMB] [numeric](19, 2) NULL,
[FreeMB] [numeric](19, 2) NULL,
)

This table is populated by a daily job. I need to write a query that will
find the FreeMB for a particular ID today, as well as the value for last day
of previous month.So ideally if I run it today, the resultset would be:

ID FreeMB Rec_Create_Date Last Month FreeMB
-- --------- ------------------ ------------ --------
102 300 Dec 03, 2008 Nov 30,2008 350


TIA,
AbbA

You can calculate the last day of the previous month by using DATEADD on the
first of the current month.
A convoluted example, I'm sure there's a quicker way:
use tempdb;

DECLARE @Today datetime;

SET @Today = GETDATE();

DECLARE @FirstOfMonthString nchar(;

SET @FirstOfMonthString = CAST(YEAR(@Today) AS NCHAR(4)) + RIGHT('0' +
CAST(MONTH(@Today) AS NVARCHAR(2)), 2) + '01';

DECLARE @FirstOfMonth datetime;

SET @FirstOfMonth = @FirstOfMonthString;

SET @FirstOfMonth = DATEADD(d, -1, @FirstOfMonth);

SELECT @FirstOfMonth FirstOfMonth;




--

Joe Fawcett (MVP - XML)

http://joe.fawcett.name


"Abba" wrote in message

> Hello,
>
> [SQL2000]
>
> CREATE TABLE [dbo].[tbl_FGSIZE]
> (
> [ID] [int] IDENTITY(1,1) NOT NULL,
> [Rec_Create_Date] [smalldatetime] NOT NULL,
> [SizeMB] [numeric](19, 2) NULL,
> [UsedMB] [numeric](19, 2) NULL,
> [FreeMB] [numeric](19, 2) NULL,
> )
>
> This table is populated by a daily job. I need to write a query that will
> find the FreeMB for a particular ID today, as well as the value for last
> day of previous month.So ideally if I run it today, the resultset would
> be:
>
> ID FreeMB Rec_Create_Date Last Month FreeMB
> -- --------- ------------------ ------------ --------
> 102 300 Dec 03, 2008 Nov 30,2008 350
>
>
> TIA,
> AbbA
>