November 29, 2013 at 12:25 am
Hi all,
I need to filter a query with the WHERE statement. The column (called CreatedOn) I need to filter contain values in the date and time format (eg: 2013-28-09 11:57:28.663)
I also need to combine this filtering with another filter where the values in the column "PropertyCode" are "PCH".
The relevant portion of my SQL Query looks like this (I am stuck on the date filter!):
........
WHERE b.PropertyCode='PCH' (I need to combine this with WHERE b.CreatedON = "2013-11-29").
Thanks for your help!
November 29, 2013 at 5:54 am
WHERE b.PropertyCode='PCH'
AND b.CreatedON BETWEEN '2013-11-29' AND '2013-11-30'
This will give you the time range '2013-11-29 00:00:00.000' till '2013-11-29-29 23:59:59.999' encompasing the whole day.
Another option is
WHERE b.PropertyCode='PCH'
AND CAST(b.CreatedON AS DATE) = '2013-11-29' but having a function on the column to be filtered will prevent the use of an index seek to find the data if an index exists on the CreatedOn column.
MCITP SQL 2005, MCSA SQL 2012
November 29, 2013 at 6:37 am
RTaylor2208 (11/29/2013)
WHERE b.PropertyCode='PCH'AND b.CreatedON BETWEEN '2013-11-29' AND '2013-11-30'
This will give you the time range '2013-11-29 00:00:00.000' till '2013-11-29-29 23:59:59.999' encompasing the whole day.
Time (and datetime as wel) is in reality a continuity, in reality it has an endless resolution, something similar to a real. Therefore I consider between a poor choice in combination with a time (or a real). The above solution gives 'wrong' answers, see the code below.
See the examples below:
create table dates (
datum datetime)
insert into dates values('1999-09-09')
insert into dates values('1999-09-09 09:09')
insert into dates values('1999-09-10')
insert into dates values('1999-09-10 09:09')
select '--' [--], * from dates
-- datum
---- -----------------------
-- 1999-09-09 00:00:00.000
-- 1999-09-09 09:09:00.000
-- 1999-09-10 00:00:00.000
-- 1999-09-10 09:09:00.000
select '--' [--], * from dates where datum between '1999-09-09' AND '1999-09-10'
-- datum
---- -----------------------
-- 1999-09-09 00:00:00.000
-- 1999-09-09 09:09:00.000
-- 1999-09-10 00:00:00.000
select '--' [--], * from dates where datum between '1999-09-09' AND '1999-09-09 23:59:59.999'
-- datum
---- -----------------------
-- 1999-09-09 00:00:00.000
-- 1999-09-09 09:09:00.000
-- 1999-09-10 00:00:00.000
select '--' [--], * from dates where datum >= '1999-09-09' and datum < '1999-09-10'
-- datum
---- -----------------------
-- 1999-09-09 00:00:00.000
-- 1999-09-09 09:09:00.000
-- For a selection with a single date or a specific enddate
declare @enddate datetime = '1999-09-09'
select '--' [--], * from dates where datum >= '1999-09-09' and datum < dateadd(dd,1,@enddate)
-- datum
---- -----------------------
-- 1999-09-09 00:00:00.000
-- 1999-09-09 09:09:00.000
drop table dates
By using a greater equal to include the beginning time and using the less then (AND NOT EQUAL) for the ending time will work in far more situations than the between. It works for hour based systems, it works for second based systems the granullity (precision) does not matter, it works in all or most programming languages.
Ben
November 29, 2013 at 5:05 pm
RTaylor2208 (11/29/2013)
WHERE b.PropertyCode='PCH'AND b.CreatedON BETWEEN '2013-11-29' AND '2013-11-30'
This will give you the time range '2013-11-29 00:00:00.000' till '2013-11-29-29 23:59:59.999' encompasing the whole day.
Another option is
WHERE b.PropertyCode='PCH'
AND CAST(b.CreatedON AS DATE) = '2013-11-29' but having a function on the column to be filtered will prevent the use of an index seek to find the data if an index exists on the CreatedOn column.
As Ben pointed out, that's actually incorrect on many front's. You should never user BETWEEN for such things because of the time element and the fact that both end points of BETWEEN are inclusive and the fact that '2013-11-29-29 23:59:59.999' simply does not exist in the DATETIME data-type. The closest that you can get to that without it automatically rounding up to the next day is '2013-11-29 23:59:59.998' and that will actually round down to '2013-11-29 23:59:59.997' because DATETIME is accurate to only 3.3 milliseconds. That means that the only possible values of a DATETIME taken to the milliseconds will always end with 0, 3, or 7 regardless of what you feed it.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 3, 2013 at 12:55 am
Thank you for your solutions guys! Sorry for getting back late on this topic.
I am still a bit confused at the final solution proposed by ben.brugman.
If I understand well, my code should be like this:
WHERE b.PropertyCode='PCH'
AND b.CreatedON >= '2013-11-29' AND < '2013-11-30'
Is that right?
December 3, 2013 at 1:10 am
hoolash (12/3/2013)
Thank you for your solutions guys! Sorry for getting back late on this topic.I am still a bit confused at the final solution proposed by ben.brugman.
If I understand well, my code should be like this:
WHERE b.PropertyCode='PCH'
AND b.CreatedON >= '2013-11-29' AND < '2013-11-30'
Is that right?
Almost!
I like to use the following construct
AND b.createdOn >= '2013-11-29' AND b.createdOn < DateAdd(dd, 1, '2013-11-29')
Of course I am using variables for the dates rather than hard coded values. This makes the function worry about month and year boundaries.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply