Date range including all years
-
How can I write a T-sql statemant that does a date range including all years?
Ex. Lets say you want a birthday list of anyone born between 2/1 and 5/15 regardless of year.
Thanks,
Eric
-
select Id
from Employee
where Right(convert(char(6),DOB,12) ,4) between '0201' and '0515'
* Noel -
I just had some fun with that idea. If you're extremely concerned about performance then you can do something like this :
IF Object_id('Employes2') > 0
DROP TABLE Employes2
GO
IF object_id('fnEmployes2') > 0
DROP FUNCTION fnEmployes2
GO
CREATE FUNCTION [dbo].[fnEmployes2] (@DOB as datetime)
RETURNS SMALLINT
WITH SCHEMABINDING
AS
BEGIN
RETURN CONVERT(SMALLINT, RIGHT(CONVERT(CHAR(8),@DOB,112), 4))
END
GO
CREATE TABLE [Employes2] (
[id] [int] NOT NULL ,
[name] [varchar] (50) ,
[DOB] [datetime] NOT NULL ,
[DOB_MD] AS dbo.fnEmployes2 (DOB)
CONSTRAINT [PK_Employes2] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX IX_Employes2_DOB ON dbo.Employes2 (DOB)
GO
INSERT INTO dbo.Employes2 (id, name, dob) Select id, cast(name as varchar(50)), CrDate from dbo.SysObjects
GO
--Select objectproperty(object_id('fnEmployes2'), 'IsDeterministic')
--1
GO
create nonclustered index IX_DayMonth_Of_Birth on dbo.Employes2 (DOB_MD)
GO
Select * from dbo.Employes2 where DOB_MD between 101 and 301
--163 row(s) affected
/*
|--Compute Scalar(DEFINE
[Employes2].[DOB_MD]=[dbo].[fnEmployes2](Convert([Employes2].[DOB]))))|--Bookmark Lookup(BOOKMARK
[Bmk1000]), OBJECT[Ideal].[dbo].[Employes2]))|--Index Seek(OBJECT
[Ideal].[dbo].[Employes2].[IX_DayMonth_Of_Birth]), SEEK[Employes2].[DOB_MD] >= Convert([@1]) AND [Employes2].[DOB_MD] > 83% of the query)*/
GO
Select * from dbo.Employes2 where RIGHT(CONVERT(CHAR(6),DOB,12), 4) between '0101' AND '0301'
--163 row(s) affected
/*
|--Compute Scalar(DEFINE
[Employes2].[DOB_MD]=[dbo].[fnEmployes2](Convert([Employes2].[DOB]))))|--Clustered Index Scan(OBJECT
[Ideal].[dbo].[Employes2].[PK_Employes2]), WHEREright(Convert(Convert([Employes2].[DOB])), 4)>='0101' AND right(Convert(Convert([Employes2].[DOB])), 4)<='0301'))53.75% of the batch
*/
GO
DROP TABLE Employes2
GO
DROP FUNCTION fnEmployes2
GO
-
Thanks for the responses
-
HTH. Was fun (read a freaking pain in the @$$) to make that cumputed column indexable
... but still worth it 
!! -
here's the birthday sort :
select * from customers
order by Datepart(mm, DOB), Datepart(dd, DOB)
and likewise for dates between 2/1 and 5/15 regardless of year...
(example from using Northwind)
select * from orders
where Datepart(mm, orderdate) between 2 and 5
and Datepart(dd, orderdate) between 1 and 15
order by Datepart(mm, orderdate), Datepart(dd, orderdate)
add proper indices for performance as needed...
you could also index computed columns (using month, day values) -- and use those instead.
http://www.7hertz.com/archives/2004/04/its_your_birthday.html
-
tzara says
select * from orders
where Datepart(mm, orderdate) between 2 and 5
and Datepart(dd, orderdate) between 1 and 15
order by Datepart(mm, orderdate), Datepart(dd, orderdate)
Yea, right, like that would pick up 02/17/1999. I like the number solutions better, but if you went this way:
select * from orders
where Datepart(mm, orderdate) between 2 and 4 or (Datepart(mm, orderdate) =5 and Datepart(dd, orderdate) <= 15)
order by Datepart(mm, orderdate), Datepart(dd, orderdate)
If it wasn't the first of February he was looking for, you'd have to have a special selection for it too.
-
I'll still stick to Noeld's solution... extremely simple and easy to understand.. Mine is just required if you query this field often and need performance.
-
Remi, I haven't seen (don't remember?) using a function to build a column in a table.
So, internally DOB_MD is stored as a smallint field. Inserting without specifying DOB_MD obviously calculates the value.
I assume updating DOB would also affect the DOB_MD value as well?
What does SQL do if you try to set the value of DOB_MD in an insert/update statement?
-
>> Datepart(mm, orderdate) between 2 and 4 or (Datepart(mm, orderdate) =5
Kenneth, what are you doing here? Between includes the range specified.
should be ...
Datepart(mm, orderdate) between 2 and 5
that includes Feb to May.
-
Never tried, but most likely fail with error. What normally happens is that the field is calculated on the fly when you query it. But in this case, I index it, so it's actually kept at least in an index (maybe in the table too, but I can't do test to find out from here).
But since you have the index to base the query on, it does an index seek this time instead of a scan... making the query slightly faster. This would be a good idea if you had a lot of range searches on that fields.. Otherwise I think it's just wasted i/o and cpu cycles
.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply