Date range selection error for a table view

  • Hi,

    I have created a view using the query below which is based on dates within my RMMarketingPrice table. The view is used to load a screen list selection with values from the current month from the 1st until the previous day which is fine unless the current date is the 1st, in which case it produces an ‘out-of-range’ error message due to the range selected.

    Create/Load table code

    USE [Test]

    GO

    /****** Object: Table [dbo].[Table_1] Script Date: 07/25/2014 13:38:54 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[RMMarketPrice](

    [Date] [smalldatetime] NOT NULL

    ) ON [PRIMARY]

    ;

    INSERT dbo.RMMarketPrice

    (

    Date

    )

    VALUES

    ('2014-06-25 00:00:00')

    ;

    INSERT dbo.RMMarketPrice

    (

    Date

    )

    VALUES

    ('2014-07-01 00:00:00')

    ;

    INSERT dbo.RMMarketPrice

    (

    Date

    )

    VALUES

    ('2014-07-12 00:00:00')

    ;

    INSERT dbo.RMMarketPrice

    (

    Date

    )

    VALUES

    ('2014-07-23 00:00:00')

    ;

    Table View code

    SELECT DISTINCT TOP (100) PERCENT Date

    FROM dbo.RMMarketPrice

    WHERE (Date BETWEEN CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '-' + RIGHT('0' + CAST(MONTH(GETDATE()) AS VARCHAR(2)), 2) + '-' + '01 00:00:00' AND

    CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '-' + RIGHT('0' + CAST(MONTH(GETDATE()) AS VARCHAR(2)), 2) + '-' + RIGHT('0' + CAST(DAY(GETDATE() - 1)

    AS VARCHAR(2)), 2) + ' 00:00:00')

    ORDER BY Date

    ;

    Any ideas how I can prevent this please.

    Thanks in advance,

  • What is the expected outcome when it is the 1st of the month?

  • Aah yes... A 'null' would be fine. Thanks 🙂

  • Just return null? No other data needs to be returned?

  • Does this do what you need?

    SELECT DISTINCT TOP (100) PERCENT Date

    FROM dbo.RMMarketPrice

    WHERE Date BETWEEN dateadd(mm, datediff(mm, 0, @date), 0) AND

    dateadd(dd, datediff(dd, 0, @date) - 1, 0)

    ORDER BY Date

  • I only want data from the current month. The selection criteria is from the 1st until the previous day (i.e. as today is the 25th, then from 01/07/2014 until 24/07/2014). But in the example where today would be the 1st of the month, I don't want any current months data returning.

    Sorry, I didn't see your previous post whilst I was typing. I'll try it now.

  • Many thanks Anthony

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply