January 24, 2008 at 8:46 am
When I run this query and enter a start date of 1/22/2008, and an end date of 1/23/2008, I get all the 1/22/2008 date, but none of the 1/23/2008 data. Why? And how can I change it? I can't just add 23:59:59 to the query, since I may want to enter and end date of 1/23/2008 4 pm, where I want to limit the report to entries up to 4 pm.
SELECT substring (TAG , 10,50) as 'ALARM NAME', VALUE,
CONVERT (CHAR(27),(TIME),109) as 'DATE & TIME'
FROM dbo.OMRON_1_ALARMS where TIME between @StartDate and @EndDate
UNION ALL
SELECT substring (TAG, 10,50) as 'ALARM NAME', VALUE,
CONVERT (CHAR(27),(TIME),109) as 'DATE & TIME'
FROM dbo.OMRON_2_ALARMS where TIME between @StartDate and @EndDate
Thanks for any help!
Gary
January 24, 2008 at 9:15 am
Either you use times or you don't. It can't be everything up to 23:99 unless I enter something else.
If you want everything up to midnight, you can use < @enddate, and send in the next day.
If you want to use times, like up to 4pm, then you have to enter them. If you're using the <@end, then send in 4:01pm
January 24, 2008 at 9:17 am
You need to alter the syntax of the time variables so you drop the time element making midnight the default ie:- BETWEEN SELECT CONVERT(CHAR(8),GETDATE(),112)
AND SELECT CONVERT(CHAR(8),GETDATE()-1,112)
January 24, 2008 at 9:22 am
That's what I was afraid of - guess I can't have my cake and eat it too!
I thinkg I'll just leave it alone and instruct our customers that they always have to enter the time, that way the report is always correct.
Thanks for your help, I won't keep looking for a solution that doesn't exist!
Gary
January 24, 2008 at 3:52 pm
January 25, 2008 at 3:03 pm
I have revisited this problem to make sure I understand what is happening. If I use just @EndDate and enter 1/23/2008, I will get data up to Midnight 1/22/2008. If I enter 1/23/2008 1 PM, then I get data up to 1/23/2008 1 PM.
If I use DATEADD(day,1,@EndDate) and enter 1/23/2008, then I get data up to midnight 1/23/2008. But if I enter 1/23/2008 1 PM, I still get data up to midnight 1/23/2008 - it ignores the 1 PM entry.
Sorry, but this makes no sense to me. I would think that entering a date would automatically include data for that whole day (until midnight), and that you could limit the data for that day to an entered hour.
If this is a 'feature' of SQL I fail to see its advantage.
Thanks for everyone's help.
Gary
January 25, 2008 at 3:42 pm
Remember that in SQL Server you are not entering dates but datetimes. This means that you must alway consider the time component in any query.
When you want data for a date range, it is usually better to do the selection in the form of:
where MyDate >= @StartDatetime and MyDate < @EndDatetime
This will allow the query to take advantage of any index on the datetime column.
For your query, I would try this:
select
*
From
MyTable
Where
MyDate >= '20080122' and MyDate < '20080124'
January 25, 2008 at 3:45 pm
Gary - could you give us a sample of what you have in the TIME column?
Also - I'm not sure you're clear on this - but '1/23/2008' converts to 1/23/2008 0:00:00.000 , which is (how to describe it vividly) "one hour before 1AM on the 23rd" and NOT "one hour before 1AM on the 24th". In other words, "midnight" for SQL Server is a concept that describes the FIRST minute of today, not the LAST minute of yesterday.
So -
between '1/22/2008' and '1/23/2008'
is the same as
between 1/22/2008 0:00:00.0000 and 1/23/2008 0:00:00.0000
Meaning - none of 1/23 is included (except for Midnight itself).
Also - using DATEADD(day,1,@enddate) doesn't truncate the hours from the result. Something else is going on.
Anyway - post some of the data and how you're populating @startdate and @enddate. Someone will likely see something.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 25, 2008 at 3:53 pm
Matt,
Here's the code:
SELECT substring (TAG , 10,50) as 'ALARM NAME',
Case when value=0 then 'RESET' else 'ON' end as 'STATUS',
CONVERT (CHAR(27),(TIME),109) as 'DATE & TIME'
FROM dbo.OMRON_1_ALARMS where TAG like '%' + @EnterAlarm + '%' and TIME between @StartDate and DATEADD(day,1,@EndDate)
I understand what you said, but from a purely practicle point of view, I think someone didn't think it through very well when they set it up initially.
Anyway, if there is a way to allow an entry of 1/24/2008 to include all the data up to midnight of 1/24/2008 and also an entry of 1/24/2008 4 PM to include all data up to 1/24/2008 4PM, I'm all ears.
Thanks for responding - I'm such a newbie at SQL that most of the readers are probably laughing. Oh well, such is life.
Gary
January 25, 2008 at 4:11 pm
Try cleaning up the @enddate before using it. In other words - assume that if the user didn't include a time component, they want all day. If they do - then don't mess with it at all.
That would look something like:
--clean up on aisle 5 - messy dates supplied
select @enddate=
case when convert(char,@enddate,108)='00:00:00'
then dateadd(day,1,@enddate) --user didn't give us a time component
else @enddate --user did give us a time component
end
--now used the cleaned up enddate
SELECT
substring(TAG , 10,50) as 'ALARM NAME',
Case when value=0 then 'RESET' else 'ON' end as 'STATUS',
CONVERT (CHAR(27),(TIME),109) as 'DATE & TIME'
FROM
dbo.OMRON_1_ALARMS
where
TAG like '%' + @EnterAlarm + '%'
and TIME >= @StartDate
and TIME<@enddate
Now - depending on where you're getting this from, you might care to change the User Interface to "encourage" them to fill it in completely. Like - put a "include all day" next to the @enddate field so that you KNOW what the user wanted, and don't assume you know. I've been burned on that one a few times - just a suggestion.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 28, 2008 at 7:09 am
Matt,
Thanks again for your help. I'll get busy on this code in a couple days (I'm on a different project momentarily)
I'll let you know the results.
Thanks,
Gary
January 28, 2008 at 8:14 am
Matt,
I stole a couple minutes to try the new code:
--clean up on aisle 5 - messy dates supplied
select @enddate=
case when convert(char,@enddate,108)='00:00:00'
then dateadd(day,1,@enddate) --user didn't give us a time component
else @enddate --user did give us a time component
end
--now used the cleaned up enddate
SELECT
substring(TAG , 10,50) as 'ALARM NAME',
Case when value=0 then 'RESET' else 'ON' end as 'STATUS',
CONVERT (CHAR(27),(TIME),109) as 'DATE & TIME'
FROM dbo.OMRON_1_ALARMS
where
TAG like '%' + @EnterAlarm + '%'
and TIME >= @StartDate
and TIME<@enddate
But I get an error:
Must declare the scaler variable "@enddate"
Must declare the scale varialbe "@Enteralarm".
I copied the code you suggested, what did I do wrong?
Thanks,
Gary
January 28, 2008 at 8:27 am
The variables need to be declared somewhere. The code I gave you would need to be inserted into your existing process (which apparently had already handled the declaration/passing of parameters.)
If that's too much - then simply add in "manual declarations"
declare @startdate datetime
declare @enddate datetime
declare @EnterAlarm integer
select @startdate = '01/08/2008' --put your test values here
select @enddate = '01/08/2008' --put your test values here
select @enteralarm = 123456 --put your test values here
--the rest of the code continues here
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 28, 2008 at 8:30 am
Matt,
Hmmm... The code you see is the entire code I use. I don't delcare anything.
Thanks,
Gary
January 28, 2008 at 8:40 am
Then you're using it in a report? Reports do the declaring - just "off the screen". Look under parameters - you'll see them declared.
The point is - if you want to test what I gave you in query analyzer or SSMS instead of the report itself, then you need those declared...
Edit: having a slow brain performance morning here....Reporting services wants a single T-SQL statement.... So - we have to combine the cleanup and the "regular select".
SELECT
substring(TAG , 10,50) as 'ALARM NAME',
Case when value=0 then 'RESET' else 'ON' end as 'STATUS',
CONVERT (CHAR(27),(TIME),109) as 'DATE & TIME'
FROM dbo.OMRON_1_ALARMS
where TAG like '%' + @EnterAlarm + '%'
and TIME >= @StartDate
and TIME<case when convert(char,@enddate,108)='00:00:00'
then dateadd(day,1,@enddate) --user didn't give us a time component
else @enddate --user did give us a time component
end
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply