Greater Than Date Less Than Another Date

Question

Greater Than Date Less Than Another Date

walter.dziuba

Ten Centuries

Points: 1045
More actions
March 2, 2017 at 2:05 pm

#322179

I'm attempting to use
DECLARE @begindate varchar(30) = '2010-03-01'
DECLARE @enddate varchar(30) = '2010-03-02'
select * from
(select FORMAT( date, 'MM/dd/yyyy', 'en-US') as [Day], FORMAT( date, 'H:00', 'en-US') as [Time], coalesce(lead(value,1) over (order by date),value) - Value as Increment
from [xxx].[xxx].[xxxx]
where date between @BeginDate AND DATEADD(mi, 1439, @EndDate) and [Series Name] = 'qqq') PRECIPACC
The statement above works fine, but is returning too many values. Instead, I need to use the >= and <= to retrieve only 24x values.
where date >= @BeginDate AND ????? DATEADD(mi, 1439, @EndDate) and [Series Name] = 'qqq') PRECIPACC
I can't seem to get the @EndDate value while using <=
Can someone help, please?
Walter

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

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

drew.allen SSC Guru Points: 76944 More actions · Answer 1

Generally when working with DATETIME data, you want to use half-open ranges. That is, you want one end of the range to include the endpoint and the other end of the range to exclude the endpoint. BETWEEN uses closed ranges (both endpoints are included). Try changing the criteria for the end point to < rather than <=.

If that doesn't work, then you need to post sample data and expected results as outlined in the first link in my signature.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Rob Buecker SSChasing Mays Points: 638 More actions · Answer 2

Your comment about 24x values, are you looking for 24 hours? It appears that you are selecting 48 hours of data.
Either update the @enddate or remove the DATEADD() if that is the case.

walter.dziuba Ten Centuries Points: 1045 More actions · Answer 3

Rob,

Yes, that is the case. I'm only searching between two particular dates. Usually, 03/01/2010 (format mm/dd/yyyy) and 03/02/2010 with the same format.

I've removed the DATEADD part, but it still doesn't like the "<" sign. As shown:

DECLARE @begindate varchar(30) = '2010-03-01'
DECLARE @enddate varchar(30) = '2010-03-02'
select * from
(select FORMAT( date, 'MM/dd/yyyy', 'en-US') as [Day], FORMAT( date, 'H:00', 'en-US') as [Time], coalesce(lead(value,1) over (order by date),value) - Value as Increment
from [xxx].[xxx].[xxxxx]
where date >= @BeginDate AND < @EndDate and [Series Name] = 'xxxx') PRECIPACC

I'm just reading on how to post some data.

Walter

walter.dziuba Ten Centuries Points: 1045 More actions · Answer 4

Gents,

Here is a small sample of my data.

--===== Create the test table with
CREATE TABLE #PrecipAcc
   (
   Date (datetime2(0))
   Value (real),
   Unit (varchar(15)
   )

--===== Setup any special required conditions especially where dates are concerned
SET DATEFORMAT DMY

--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #mytable ON

--===== Insert the test data into the test table
INSERT INTO #PrecipAcc
(Date, Value, Unit)
SELECT '2010-03-01 00:00:00',252.31,'mm' UNION ALL

SELECT '2010-03-02 00:01:00',252.45,'mm' UNION ALL

SELECT '2010-03-04 11:01:00',252.5,'mm' UNION ALL

Walter

sgmunson SSC Guru Points: 110639 More actions · Answer 5

walter.dziuba - Thursday, March 2, 2017 2:05 PM
I'm attempting to use
DECLARE @begindate varchar(30) = '2010-03-01'
DECLARE @enddate varchar(30) = '2010-03-02'
select * from
(select FORMAT( date, 'MM/dd/yyyy', 'en-US') as [Day], FORMAT( date, 'H:00', 'en-US') as [Time], coalesce(lead(value,1) over (order by date),value) - Value as Increment
from [xxx].[xxx].[xxxx]
where date between @BeginDate AND DATEADD(mi, 1439, @EndDate) and [Series Name] = 'qqq') PRECIPACC
The statement above works fine, but is returning too many values. Instead, I need to use the >= and <= to retrieve only 24x values.
where date >= @BeginDate AND ????? DATEADD(mi, 1439, @EndDate) and [Series Name] = 'qqq') PRECIPACC
I can't seem to get the @EndDate value while using <=
Can someone help, please?
Walter

DECLARE @begindate varchar(30) = '2010-03-01' DECLARE @enddate varchar(30) = '2010-03-02' select * from (select FORMAT( date, 'MM/dd/yyyy', 'en-US') as [Day], FORMAT( date, 'H:00', 'en-US') as [Time], coalesce(lead(value,1) over (order by date),value) - Value as Increment from [xxx].[xxx].[xxxxx] where date >= @BeginDate AND date < @EndDate and [Series Name] = 'xxxx') PRECIPACC

Look at the highlighted text...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

walter.dziuba Ten Centuries Points: 1045 More actions · Answer 6

Steve,

Thank you so much for helping out. And yes, your suggestion worked like a charm.

Appreciate it.....Walter