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