Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Date range selection error for a table view Expand / Collapse
Author
Message
Posted Friday, July 25, 2014 8:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 4:05 AM
Points: 152, Visits: 853
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,
Post #1596236
Posted Friday, July 25, 2014 8:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, November 1, 2014 6:54 AM
Points: 5,221, Visits: 5,118
What is the expected outcome when it is the 1st of the month?



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1596247
Posted Friday, July 25, 2014 8:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 4:05 AM
Points: 152, Visits: 853
Aah yes... A 'null' would be fine. Thanks
Post #1596262
Posted Friday, July 25, 2014 9:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, November 1, 2014 6:54 AM
Points: 5,221, Visits: 5,118
Just return null? No other data needs to be returned?



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1596265
Posted Friday, July 25, 2014 9:13 AM This worked for the OP Answer marked as solution


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, November 1, 2014 6:54 AM
Points: 5,221, Visits: 5,118
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





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1596271
Posted Friday, July 25, 2014 9:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 4:05 AM
Points: 152, Visits: 853
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.
Post #1596272
Posted Friday, July 25, 2014 9:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 4:05 AM
Points: 152, Visits: 853
Many thanks Anthony
Post #1596275
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse