locked
sql query - between two dates RRS feed

  • Question

  • In a database I have a table of Students. One of the columns is Birthday. Now I would like to do the query which would return the students which are born between two dates. But important here is that the year has to be excluded. Only the days and months will be the parameters.

    For example I would like to get students between 28th of June and 6 of July (in my country we use date as dd.MM.yyyy, so it has to be between 28.6 - 6.7). Repeat the the year they are born it is not important.

    I did the querry which looks like this, but its not working, there is no errors, just the dataTable doesn`t fill up:

    string myQuery = "SELECT StudentName FROM Students WHERE " +
             "MONTH(Birth) BETWEEN @monthFrom AND @monthTo AND " +
             "DAY(Birth) BETWEEN @dayFrom AND @dayTo;

    All the parameters are integers.

    But what`s important on this querry is, if I exclude the DAY, the querry gets values (it finds the students which are borh in June and July). But this is not enough, I would like to get values between 28 of June and 6 of  July (this is one week apart).

    Monday, June 28, 2010 1:01 PM

Answers

  • If Birth column is DATETIME

    string myQuery = "SELECT StudentName FROM Students WHERE " +
             "CONVERT(CHAR(5), Birth, 110) BETWEEN REPLACE(STR(@monthFrom, 2), ' ', '0') + '-' + REPLACE(STR(@dayFrom, 2), ' ', '0') AND REPLACE(STR(@monthTo, 2), ' ', '0') + '-' + REPLACE(STR(@dayTo, 2), ' ', '0');"

    • Marked as answer by Mitja Bonca Monday, June 28, 2010 1:39 PM
    Monday, June 28, 2010 1:08 PM
  • Try:

    ... where (month(birth) * 100) + day(birth) between (@monthFrom * 100) + @dayFrom and (@monthTo * 100) + @dayTo;

    You can create a computed column to persist this value, and create an index by this column. This way, you do not have to process all rows in order to calculate the formula each time you execute this query.

    AMB

    Monday, June 28, 2010 1:11 PM

All replies

  • Try:

    ... where (month(birth) * 100) + day(birth) between (@monthFrom * 100) + @dayFrom and (@monthTo * 100) + @dayTo;

    You can create a computed column to persist this value, and create an index by this column. This way, you do not have to process all rows in order to calculate the formula each time you execute this query.

    AMB

    Monday, June 28, 2010 1:11 PM
  • Damn, this looks complicsted :)

    But I will going to try it out right away...

    btw, can you explain why my query is not working? I am just curious.

    Monday, June 28, 2010 1:12 PM
  • Try:

    ... where (month(birth) * 100) + day(birth) between (month(@monthFrom) * 100) + day(@dayFrom) and (month(@monthTo) * 100) + day(@dayTo);

    You can create a computer column to persist this value, and create an index by this column. This way, you do not have to process all rows in order to calculate the formula each time you execute this query.

    AMB

    Why do you use there * 100?
    Monday, June 28, 2010 1:19 PM
  • Damn, this looks complicsted :)

    But I will going to try it out right away...

    btw, can you explain why my query is not working? I am just curious.


    The operator "between" (x between y and z) is expanded by the query processor as "x >= y and x<= z", so if the second operand is less than the first one, then the expression will be false.

    Example:

    "29 between 28 and 6" will be translated or expanded to "29 >= 28 and 29 <= 6".

    AMB

    Monday, June 28, 2010 1:19 PM
  • Try:

    ... where (month(birth) * 100) + day(birth) between (month(@monthFrom) * 100) + day(@dayFrom) and (month(@monthTo) * 100) + day(@dayTo);

    You can create a computer column to persist this value, and create an index by this column. This way, you do not have to process all rows in order to calculate the formula each time you execute this query.

    AMB

    Why do you use there * 100?

    This formula creates a sequence based on the month and the day.

    6 - 28 --> 628

    6 - 29 --> 629

    6 - 30 -- 630

    7 - 1 --> 701

    7 - 2 --> 702

    ...

    Now you can inquiry if a number is between two numbers in that sequence, like "629 between 628 and 706".

     

    AMB

    Monday, June 28, 2010 1:23 PM
  •       DECLARE @STUDENTS TABLE

          (

          Studentid INT

          ,Name     VARCHAR(50)

          ,Birth        DateTime

          )

         

          INSERT INTO @STUDENTS

          SELECT 1,'A','30-JUN-1990'

          UNION

          SELECT 1,'A','02-JUL-1990'

         

          DECLARE @Monthfrom INT

          DECLARE @MonthTo INT

          DECLARE @DateFrom INT

          DECLARE @DateTo INT

         

          SELECT @Monthfrom = 6

          SELECT @MonthTo = 7

          SELECT @DateFrom = 28

          SELECT @DateTo = 1

         

          SELECT *

            FROM @STUDENTS

           WHERE CONVERT(DATETIME,CONVERT(VARCHAR(2),Day(Birth)) +'.' + CONVERT(VARCHAR(2),Month(Birth)) + '.9999',104)

                 BETWEEN CONVERT(DATETIME,CONVERT(VARCHAR(2),@DateFrom) + '.' + CONVERT(VARCHAR(2),@MonthFrom) + '.9999',104)

                     AND CONVERT(DATETIME,CONVERT(VARCHAR(2),@DateTo) + '.' + CONVERT(VARCHAR(2),@MonthTo) + '.9999',104)

                

                

                

    Monday, June 28, 2010 1:26 PM
  • Hunchback, you query doesn`t seem to work, when Peso`s does. The dataTable doesnt will up (but not errors). 

    Any Clues why?

    Monday, June 28, 2010 1:28 PM
  • Why do you use there * 100?

    This formula creates a sequence based on the month and the day.

    6 - 28 --> 628

    6 - 29 --> 629

    6 - 30 -- 630

    7 - 1 --> 701

    7 - 2 --> 702

    ...

    Now you can inquiry if a number is between two numbers in that sequence, like "629 between 628 and 706".

     

    AMB

    This is how dateTime works? So this is more clear to me now. And how about if there is a year behind? I am just curious.
    Monday, June 28, 2010 1:31 PM
  • Hunchback, you query doesn`t seem to work, when Peso`s does. The dataTable doesnt will up (but not errors). 

    Any Clues why?


    Can you post the table schema, sample data,  and the query, please?

    USE tempdb;
    GO
    SET NOCOUNT ON;
    GO
    DECLARE @T TABLE (
    employeeid int NOT NULL PRIMARY KEY,
    birthdate datetime NULL
    );
    
    INSERT INTO @T (employeeid, birthdate) values(1, '19481208');
    INSERT INTO @T (employeeid, birthdate) values(2, '19520219');
    INSERT INTO @T (employeeid, birthdate) values(3, '19630830');
    INSERT INTO @T (employeeid, birthdate) values(4, '19370919');
    INSERT INTO @T (employeeid, birthdate) values(5, '19550304');
    INSERT INTO @T (employeeid, birthdate) values(6, '19630702');
    INSERT INTO @T (employeeid, birthdate) values(7, '19600529');
    INSERT INTO @T (employeeid, birthdate) values(8, '19580109');
    INSERT INTO @T (employeeid, birthdate) values(9, '19660127');
    
    DECLARE @mFrom int, @dFrom int, @mTo int, @dTo int;
    
    SELECT @mFrom = 6, @dFrom = 28, @mTo = 7, @dTo = 6
    
    SELECT *
    FROM @T
    WHERE (MONTH(birthdate) * 100) + DAY(birthdate) BETWEEN (@mFrom * 100) + @dFrom AND (@mTo * 100) + @dTo;
    GO
    SET NOCOUNT OFF;
    GO

    AMB

    Monday, June 28, 2010 1:40 PM
  • 1. Table schema:
    IdStudent         int                  Unchecked
    StudentName    varchar(50)    Checked
    Birth                datetime         Checked


    2. Sample data:
    1 Mitja Bonca 27.6.1979 0:00:00
    2 Natasa Logar 27.6.1988 0:00:00
    3 Sandra Bonca 28.6.1981 0:00:00
    4 Kr En Kekec 30.6.1991 0:00:00
    5 Tone Seliskar 2.7.1975 0:00:00
    6 Janez Trcek 6.7.1977 0:00:00
    7 Karmen Logar 8.8.1995 0:00:00
    NULL NULL

    NULL




    3. Query: (the one you posted):

    string myQuery = 
    "SELECT StudentName FROM Students WHERE " +
    "where (month(birth) * 100) + day(birth) between " +
    "(month(@monthFrom) * 100) + day(@dayFrom) and " +
    "(month(@monthTo) * 100) + day(@dayTo)";


    I see now:
    - if there is only a day , for examle 3rd of a month, the number is 3 (or 003)
    - if there is a day and a months, 3rd of May, the number is 503
    if there is a day, month and a year, 3rd of May 2010, the number is 20100503 (if month has only 1 integer, there has to be 0 infront, so 05 for may)

    Am I right? 
    Monday, June 28, 2010 1:44 PM
  • 1. Table schema:
    IdStudent         int                  Unchecked
    StudentName    varchar(50)    Checked
    Birth                datetime         Checked


    2. Sample data:
    1 Mitja Bonca 27.6.1979 0:00:00
    2 Natasa Logar 27.6.1988 0:00:00
    3 Sandra Bonca 28.6.1981 0:00:00
    4 Kr En Kekec 30.6.1991 0:00:00
    5 Tone Seliskar 2.7.1975 0:00:00
    6 Janez Trcek 6.7.1977 0:00:00
    7 Karmen Logar 8.8.1995 0:00:00
    NULL NULL

    NULL




    3. Query: (the one you posted):

    string myQuery = 
    
    "SELECT StudentName FROM Students WHERE " +
    
    "where (month(birth) * 100) + day(birth) between " +
    
    "(month(@monthFrom) * 100) + day(@dayFrom) and " +
    
    "(month(@monthTo) * 100) + day(@dayTo)";
    
    


    I see where the confusion is. The parameters are integers and not datetime, so the formula should be:

    (month(dt) * 100) + day(dt) between (@mf * 100) + @df and (@mt * 100) + @dt

    USE tempdb;
    GO
    CREATE TABLE #T (
    IdStudent int,
    StudentName varchar(50),
    Birth datetime
    );
    
    INSERT INTO #T VALUES(1, 'Mitja Bonca', '19790627');
    INSERT INTO #T VALUES(2, 'Natasa Logar', '19880627'); 
    INSERT INTO #T VALUES(3, 'Sandra Bonca', '19810628'); 
    INSERT INTO #T VALUES(4, 'Kr En Kekec', '19910630'); 
    INSERT INTO #T VALUES(5, 'Tone Seliskar', '19750702'); 
    INSERT INTO #T VALUES(6, 'Janez Trcek', '19770706'); 
    INSERT INTO #T VALUES(7, 'Karmen Logar', '19950808'); 
    
    DECLARE @myQuery nvarchar(MAX);
    
    SET @myQuery = 'SELECT * FROM #T WHERE 
    (MONTH(birth) * 100) + DAY(birth) BETWEEN 
    (@monthFrom * 100) + @dayFrom AND 
    (@monthTo * 100) + @dayTo;';
    
    EXEC sp_executesql @myQuery, N'@monthFrom int, @dayFrom int, @monthTo int, @dayTo int', 6, 28, 7, 6;
    
    DROP TABLE #T;
    GO
    

    Sorry about that.

    AMB

    Monday, June 28, 2010 2:13 PM
  • Check also

    http://berezniker.com/content/pages/sql/microsoft-sql-server/birthday-query-ms-sql-server

     

    DECLARE @StartDate DATETIME, @EndDate DATETIME
     
    SET @StartDate = '2009-02-22'
    SET @EndDate  = '2009-02-28'
     
    --SET @StartDate = '2008-02-22'
    --SET @EndDate  = '2008-02-29'
     
     
    SELECT FullName, DATEPART(MONTH, dob) AS MONTH, DATEPART(DAY, dob) AS DAY, CONVERT(VARCHAR(10), dob, 111) AS dob
    	FROM People
    	WHERE	DATEADD(YEAR, DATEDIFF(YEAR, dob, @StartDate), dob) BETWEEN @StartDate AND @EndDate
    	   OR DATEADD(YEAR, DATEDIFF(YEAR, dob, @EndDate), dob) BETWEEN @StartDate AND @EndDate
    	ORDER BY CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, dob, @StartDate), dob) BETWEEN @StartDate AND @EndDate THEN 1 ELSE 2 END, 
    		DATEPART(MONTH, dob), DATEPART(DAY, dob)
    


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, June 28, 2010 2:40 PM
  • Thank you very much Hunchback. Now it works. I finally understand by my self too. It`s not so hard :) ... now after you showed and very well explained it to me.

    cheers,

    Mitja

    Monday, June 28, 2010 2:46 PM
  • >  I finally understand by my self too. It`s not so hard :) ... now after you showed and very well explained it to me.

    Great, that is what this forum is all about.

    You are welcome, Mitja.

    AMB

    Monday, June 28, 2010 2:52 PM
  • I will test your solution as wll Naom, but give me some time, too busy atm. 

    anyway, if nothing else Hunchbask`s query seems understandable to me and the simpliest of all, so I will stick to it for sure.

    Monday, June 28, 2010 3:19 PM