Get results between 2 date fields?

• mick burden

  SSCarpal Tunnel

  Points: 4432

  More actions

  January 22, 2019 at 4:47 am

  #382605

  there is 2 date fields with a from date and a to date, is it possible to find the record where if you pass in a date it works out which record it falls between? I'll put my sql below to help explain what I'm after

  
USE [testdb]
GO
SET ANSI_NULLS ON
GO

  SET QUOTED_IDENTIFIER ON
  GO

  CREATE TABLE [dbo].[testTable](
   [recid] [int] IDENTITY(1,1) NOT NULL,
   [Date1] [smalldatetime] NULL,
   [Date2] [smalldatetime] NULL
  ) ON [PRIMARY]

  GO

   INSERT INTO testTable(date1,date2)
   VALUES('01/jan/2019','11/jan/2019')
   INSERT INTO testTable(date1,date2)
   VALUES('13/jan/2019','21/jan/2019')
    INSERT INTO testTable(date1,date2)
   VALUES('01/feb/2019','15/feb/2019')
    INSERT INTO testTable(date1,date2)
   VALUES('12/mar/2019','21/mar/2019')
    INSERT INTO testTable(date1,date2)
   VALUES('09/apr/2019','15/apr/2019')

  so if I pass in a date such as 14/mar/2019 I would like it to return the recid field as my date fell in between the fromdate of '12/mar/2019'
  and the todate of '21/mar/2019'
  if that is even possible ?
  

• Neil Burton

  SSC-Insane

  Points: 23002

  More actions

  January 22, 2019 at 5:15 am

  #2019613

  mick burden - Tuesday, January 22, 2019 4:47 AM

  there is 2 date fields with a from date and a to date, is it possible to find the record where if you pass in a date it works out which record it falls between? I'll put my sql below to help explain what I'm after

  
USE [testdb]
GO
SET ANSI_NULLS ON
GO

  SET QUOTED_IDENTIFIER ON
  GO

  CREATE TABLE [dbo].[testTable](
   [recid] [int] IDENTITY(1,1) NOT NULL,
   [Date1] [smalldatetime] NULL,
   [Date2] [smalldatetime] NULL
  ) ON [PRIMARY]

  GO

   INSERT INTO testTable(date1,date2)
   VALUES('01/jan/2019','11/jan/2019')
   INSERT INTO testTable(date1,date2)
   VALUES('13/jan/2019','21/jan/2019')
    INSERT INTO testTable(date1,date2)
   VALUES('01/feb/2019','15/feb/2019')
    INSERT INTO testTable(date1,date2)
   VALUES('12/mar/2019','21/mar/2019')
    INSERT INTO testTable(date1,date2)
   VALUES('09/apr/2019','15/apr/2019')

  so if I pass in a date such as 14/mar/2019 I would like it to return the recid field as my date fell in between the fromdate of '12/mar/2019'
  and the todate of '21/mar/2019'
  if that is even possible ?
  

  DECLARE @Date SMALLDATETIME = '14/mar/2019';

  SELECT
Recid
FROM testTable

  WHERE
    date1 <= @Date
AND Date2 >= @Date; 

  That'll do it.  If you're only holding dates it makes more sense to use the DATE datatype rather SMALLDATETIME. 

  ---

  On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
  —Charles Babbage, Passages from the Life of a Philosopher

  How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

• mick burden

  SSCarpal Tunnel

  Points: 4432

  More actions

  January 22, 2019 at 5:36 am

  #2019618

  Neil Burton - Tuesday, January 22, 2019 5:14 AM

  mick burden - Tuesday, January 22, 2019 4:47 AM

  there is 2 date fields with a from date and a to date, is it possible to find the record where if you pass in a date it works out which record it falls between? I'll put my sql below to help explain what I'm after

  
USE [testdb]
GO
SET ANSI_NULLS ON
GO

  SET QUOTED_IDENTIFIER ON
  GO

  CREATE TABLE [dbo].[testTable](
   [recid] [int] IDENTITY(1,1) NOT NULL,
   [Date1] [smalldatetime] NULL,
   [Date2] [smalldatetime] NULL
  ) ON [PRIMARY]

  GO

   INSERT INTO testTable(date1,date2)
   VALUES('01/jan/2019','11/jan/2019')
   INSERT INTO testTable(date1,date2)
   VALUES('13/jan/2019','21/jan/2019')
    INSERT INTO testTable(date1,date2)
   VALUES('01/feb/2019','15/feb/2019')
    INSERT INTO testTable(date1,date2)
   VALUES('12/mar/2019','21/mar/2019')
    INSERT INTO testTable(date1,date2)
   VALUES('09/apr/2019','15/apr/2019')

  so if I pass in a date such as 14/mar/2019 I would like it to return the recid field as my date fell in between the fromdate of '12/mar/2019'
  and the todate of '21/mar/2019'
  if that is even possible ?
  

  DECLARE @Date SMALLDATETIME = '14/mar/2019';

  SELECT
Recid
FROM testTable

  WHERE
    date1 <= @Date
AND Date2 >= @Date; 

  That'll do it.  If you're only holding dates it makes more sense to use the DATE datatype rather SMALLDATETIME. 

  That is brilliant, many thanks. Have a good day

  Mick

• Neil Burton

  SSC-Insane

  Points: 23002

  More actions

  January 22, 2019 at 6:44 am

  #2019626

  You're welcome.  I should have asked a couple of questions though.  The first is whether your sample ranges are supposed to be non-contiguous?  12 Jan 2019 doesn't fall in any range you've provided.  Second, are your date1 and date2 columns inclusive?  I know you said 'between' but does that mean 'on or after' or 'after' and conversely is it 'on' or 'on or before?

  ---

  On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
  —Charles Babbage, Passages from the Life of a Philosopher

  How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

• mick burden

  SSCarpal Tunnel

  Points: 4432

  More actions

  January 23, 2019 at 4:00 am

  #2019784

  Neil Burton - Tuesday, January 22, 2019 6:44 AM

  You're welcome.  I should have asked a couple of questions though.  The first is whether your sample ranges are supposed to be non-contiguous?  12 Jan 2019 doesn't fall in any range you've provided.  Second, are your date1 and date2 columns inclusive?  I know you said 'between' but does that mean 'on or after' or 'after' and conversely is it 'on' or 'on or before?

  I'm not entirely sure what you are asking me here, all I can say is it works exactly how I want it too, so thanks again

  Mick

• Neil Burton

  SSC-Insane

  Points: 23002

  More actions

  January 23, 2019 at 4:50 am

  #2019785

  mick burden - Wednesday, January 23, 2019 4:00 AM

  Neil Burton - Tuesday, January 22, 2019 6:44 AM

  You're welcome.  I should have asked a couple of questions though.  The first is whether your sample ranges are supposed to be non-contiguous?  12 Jan 2019 doesn't fall in any range you've provided.  Second, are your date1 and date2 columns inclusive?  I know you said 'between' but does that mean 'on or after' or 'after' and conversely is it 'on' or 'on or before?

  I'm not entirely sure what you are asking me here, all I can say is it works exactly how I want it too, so thanks again

  Mick

  If it does what you want then fair enough.  I'm just trying to make sure you don't get caught out by something you haven't thought of.  

  The first point is that you have gaps in your date ranges.  The first range runs from 01/Jan/19 to 11/Jan/19 but your second runs from the 13/Jan/19 until 21/Jan/19.  What happens if you get a date to test that falls on 12/Jan/19?  That's in neither range and won't return anything, is this right?

  The second question is, if the test date is 13/Jan/19 is that to be included in the second range or are you only interested in dates from 14/Jan/19 to 20/Jan/19?  I think you want to count the start and end dates but it's always better to check!

  ---

  On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
  —Charles Babbage, Passages from the Life of a Philosopher

  How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  January 23, 2019 at 8:48 am

  #2019827

  Neil Burton - Tuesday, January 22, 2019 5:14 AM

  mick burden - Tuesday, January 22, 2019 4:47 AM

  there is 2 date fields with a from date and a to date, is it possible to find the record where if you pass in a date it works out which record it falls between? I'll put my sql below to help explain what I'm after

  
USE [testdb]
GO
SET ANSI_NULLS ON
GO

  SET QUOTED_IDENTIFIER ON
  GO

  CREATE TABLE [dbo].[testTable](
   [recid] [int] IDENTITY(1,1) NOT NULL,
   [Date1] [smalldatetime] NULL,
   [Date2] [smalldatetime] NULL
  ) ON [PRIMARY]

  GO

   INSERT INTO testTable(date1,date2)
   VALUES('01/jan/2019','11/jan/2019')
   INSERT INTO testTable(date1,date2)
   VALUES('13/jan/2019','21/jan/2019')
    INSERT INTO testTable(date1,date2)
   VALUES('01/feb/2019','15/feb/2019')
    INSERT INTO testTable(date1,date2)
   VALUES('12/mar/2019','21/mar/2019')
    INSERT INTO testTable(date1,date2)
   VALUES('09/apr/2019','15/apr/2019')

  so if I pass in a date such as 14/mar/2019 I would like it to return the recid field as my date fell in between the fromdate of '12/mar/2019'
  and the todate of '21/mar/2019'
  if that is even possible ?
  

  DECLARE @Date SMALLDATETIME = '14/mar/2019';

  SELECT
Recid
FROM testTable

  WHERE
    date1 <= @Date
AND Date2 >= @Date; 

  That'll do it.  If you're only holding dates it makes more sense to use the DATE datatype rather SMALLDATETIME. 

  You could also use BETWEEN to shorten the code.
  
DECLARE @Date SMALLDATETIME = '20190314'; --This format is language settings independent.

  SELECT Recid 
FROM testTable
WHERE @Date BETWEEN date1 AND Date2; 


  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• mick burden

  SSCarpal Tunnel

  Points: 4432

  More actions

  January 25, 2019 at 3:49 am

  #2020080

  Luis Cazares - Wednesday, January 23, 2019 8:48 AM

  Neil Burton - Tuesday, January 22, 2019 5:14 AM

  mick burden - Tuesday, January 22, 2019 4:47 AM

  there is 2 date fields with a from date and a to date, is it possible to find the record where if you pass in a date it works out which record it falls between? I'll put my sql below to help explain what I'm after

  
USE [testdb]
GO
SET ANSI_NULLS ON
GO

  SET QUOTED_IDENTIFIER ON
  GO

  CREATE TABLE [dbo].[testTable](
   [recid] [int] IDENTITY(1,1) NOT NULL,
   [Date1] [smalldatetime] NULL,
   [Date2] [smalldatetime] NULL
  ) ON [PRIMARY]

  GO

   INSERT INTO testTable(date1,date2)
   VALUES('01/jan/2019','11/jan/2019')
   INSERT INTO testTable(date1,date2)
   VALUES('13/jan/2019','21/jan/2019')
    INSERT INTO testTable(date1,date2)
   VALUES('01/feb/2019','15/feb/2019')
    INSERT INTO testTable(date1,date2)
   VALUES('12/mar/2019','21/mar/2019')
    INSERT INTO testTable(date1,date2)
   VALUES('09/apr/2019','15/apr/2019')

  so if I pass in a date such as 14/mar/2019 I would like it to return the recid field as my date fell in between the fromdate of '12/mar/2019'
  and the todate of '21/mar/2019'
  if that is even possible ?
  

  DECLARE @Date SMALLDATETIME = '14/mar/2019';

  SELECT
Recid
FROM testTable

  WHERE
    date1 <= @Date
AND Date2 >= @Date; 

  That'll do it.  If you're only holding dates it makes more sense to use the DATE datatype rather SMALLDATETIME. 

  You could also use BETWEEN to shorten the code.
  
DECLARE @Date SMALLDATETIME = '20190314'; --This format is language settings independent.

  SELECT Recid 
FROM testTable
WHERE @Date BETWEEN date1 AND Date2; 


  many thanks Luis