July 25, 2014 at 8:25 am
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,
July 25, 2014 at 8:42 am
What is the expected outcome when it is the 1st of the month?
July 25, 2014 at 8:58 am
Aah yes... A 'null' would be fine. Thanks
July 25, 2014 at 9:00 am
Just return null? No other data needs to be returned?
July 25, 2014 at 9:13 am
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
July 25, 2014 at 9:14 am
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.
July 25, 2014 at 9:18 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy