Search with Date Range

• VegasL

  Hall of Fame

  Points: 3006

  More actions

  November 18, 2017 at 9:33 am

  #324578

  Hello,

  I have

  create table #test_table ([Date] datetime, [KW] nvarchar(255),[PP] float, [PM] nvarchar(255), [CP] float,[Δ T] float, URL nvarchar(255) )

  INSERT INTO #test_table
([Date], [KW], [PP], [PM], [CP], [Δ T], )

  VALUES
('2016-11-01 00:00:00.000', 'voucher', 10, 'up', 7, 800, 'http://www.test.com/p1/'),
('2016-12-01 00:00:00.000', 'voucher', 5, 'up', 10, -300, 'http://www.test.com/p1/'),
('2017-02-04 00:00:00.000', 'voucher', 11, 'up', 10, 1000, 'http://www.test.com/p1/'),
('2017-02-06 00:00:00.000', 'voucher', 3, 'up', 1, 500, 'http://www.test.com/p1/'),
('2017-02-10 00:00:00.000', 'voucher', 15,'down', 5,-2500, 'http://www.test.com/p1/')

  drop table #test_table

  What I want to do is select all, but within a date range  2016-12-01  through 2017-02-06?

  Thanks

• Eirikur Eiriksson

  SSC Guru

  Points: 182915

  More actions

  November 18, 2017 at 9:38 am

  #1968643

  VegasL - Saturday, November 18, 2017 9:33 AM

  Hello,

  I have

  create table #test_table ([Date] datetime, [KW] nvarchar(255),[PP] float, [PM] nvarchar(255), [CP] float,[Δ T] float, URL nvarchar(255) )

  INSERT INTO #test_table
([Date], [KW], [PP], [PM], [CP], [Δ T], )

  VALUES
('2016-11-01 00:00:00.000', 'voucher', 10, 'up', 7, 800, 'http://www.test.com/p1/'),
('2016-12-01 00:00:00.000', 'voucher', 5, 'up', 10, -300, 'http://www.test.com/p1/'),
('2017-02-04 00:00:00.000', 'voucher', 11, 'up', 10, 1000, 'http://www.test.com/p1/'),
('2017-02-06 00:00:00.000', 'voucher', 3, 'up', 1, 500, 'http://www.test.com/p1/'),
('2017-02-10 00:00:00.000', 'voucher', 15,'down', 5,-2500, 'http://www.test.com/p1/')

  drop table #test_table

  What I want to do is select all, but within a date range  2016-12-01  through 2017-02-06?

  Thanks

  So where date is < than x and > than Y ?
  😎

  Can you elaborate on the problem and post what you have tried so far please?

• VegasL

  Hall of Fame

  Points: 3006

  More actions

  November 18, 2017 at 9:50 am

  #1968645

  Hi Erik,

  Here's what I tried:

  select *
  from #test_table
  where [Date] between '2016-12%' and '2017-02%'

• Thom A

  SSC Guru

  Points: 99133

  More actions

  November 18, 2017 at 10:26 am

  #1968646

  VegasL - Saturday, November 18, 2017 9:50 AM

  Hi Erik,

  Here's what I tried:

  select *
  from #test_table
  where [Date] between '2016-12%' and '2017-02%'

  Why are you using wildcards? That's for LIKE expressions, not for literals. Use proper date values. Also, if your data contains times (which your data above suggests it doesn't), you should use >= and <, rather than between.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• VegasL

  Hall of Fame

  Points: 3006

  More actions

  November 18, 2017 at 10:33 am

  #1968647

  Ok got it. thanks for the tip.

  select *
  from #test_table
  where [Date] >='2016-12-01' and [Date] <'2017-02-10'

  How can I get the actual result to just show the date without the time stamp?

• Thom A

  SSC Guru

  Points: 99133

  More actions

  November 18, 2017 at 10:36 am

  #1968648

  VegasL - Saturday, November 18, 2017 10:33 AM

  Ok got it. thanks for the tip.

  select *
  from #test_table
  where [Date] >='2016-12-01' and [Date] <'2017-02-10'

  How can I get the actual result to just show the date without the time stamp?

  CAST or CONVERT the column to the date type in your SELECT. Better yet, if you never need to store times for your dates, change your column from a datetime to a date data type. 🙂

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• VegasL

  Hall of Fame

  Points: 3006

  More actions

  November 18, 2017 at 10:58 am

  #1968649

  Do you mean?
  like below:

  select convert([date] VARCHAR(10), GETDATE(), 101)
  from #test_table
  where [Date] >='2016-12-01' and [Date] <'2017-02-10'

• Thom A

  SSC Guru

  Points: 99133

  More actions

  November 18, 2017 at 11:00 am

  #1968650

  Yes, but replace GETDATE() with your column. You also don't need the third parameter, unless you're converting to an (n)varchar. A date will always be returned in the format yyyy-MM-dd

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• VegasL

  Hall of Fame

  Points: 3006

  More actions

  November 18, 2017 at 11:06 am

  #1968652

  Thanks Thom.

  I tried
  select convert([date] VARCHAR(10), GETDATE([date])
  from #test_table
  where [Date] >='2016-12-01' and [Date] <'2017-02-10'

  getting invalid syntax near varchar.

  Also how can I get to be returned in the format MM-dd-yyyy? 

  Appreciate you're help

• Thom A

  SSC Guru

  Points: 99133

  More actions

  November 18, 2017 at 11:12 am

  #1968653

  VegasL - Saturday, November 18, 2017 11:06 AM

  Also how can I get to be returned in the format MM-dd-yyyy? 

  Appreciate you're help

  As I said in your other topic, worry about the display format of the date in your presentation layer.

  The syntax is wrong because you're passing 2 datatypes; the parameter only accepts one.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  November 21, 2017 at 10:13 am

  #1969039

  VegasL - Saturday, November 18, 2017 9:33 AM

  Hello,

  I have

  create table #test_table ([Date] datetime, [KW] nvarchar(255),[PP] float, [PM] nvarchar(255), [CP] float,[Δ T] float, URL nvarchar(255) )

  INSERT INTO #test_table
([Date], [KW], [PP], [PM], [CP], [Δ T], )

  VALUES
('2016-11-01 00:00:00.000', 'voucher', 10, 'up', 7, 800, 'http://www.test.com/p1/'),
('2016-12-01 00:00:00.000', 'voucher', 5, 'up', 10, -300, 'http://www.test.com/p1/'),
('2017-02-04 00:00:00.000', 'voucher', 11, 'up', 10, 1000, 'http://www.test.com/p1/'),
('2017-02-06 00:00:00.000', 'voucher', 3, 'up', 1, 500, 'http://www.test.com/p1/'),
('2017-02-10 00:00:00.000', 'voucher', 15,'down', 5,-2500, 'http://www.test.com/p1/')

  drop table #test_table

  What I want to do is select all, but within a date range  2016-12-01  through 2017-02-06?

  Thanks

  Combining the original request with the ideas presented so far, here's a complete setup and query:
  CREATE TABLE #test_table (
    [Date] date,
    KW nvarchar(255),
    PP float,
    PM nvarchar(255),
    CP float,
    [Δ T] float,
    URL nvarchar(255)
);
INSERT INTO #test_table ([Date], KW, PP, PM, CP, [Δ T], URL)
    VALUES    ('2016-11-01', 'voucher', 10, 'up', 7, 800, 'http://www.test.com/p1/'),
            ('2016-12-01', 'voucher', 5, 'up', 10, -300, 'http://www.test.com/p1/'),
            ('2017-02-04', 'voucher', 11, 'up', 10, 1000, 'http://www.test.com/p1/'),
            ('2017-02-06', 'voucher', 3, 'up', 1, 500, 'http://www.test.com/p1/'),
            ('2017-02-10', 'voucher', 15,'down', 5,-2500, 'http://www.test.com/p1/');

  SELECT *
FROM #test_table
WHERE [Date] BETWEEN '2016-12-01' AND '2017-02-06';

  DROP TABLE #test_table;

  Here's the results:
  

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• VegasL

  Hall of Fame

  Points: 3006

  More actions

  November 25, 2017 at 1:10 pm

  #1969439

  Thanks Steve - Helpful

• HappyGeek

  SSCoach

  Points: 18729

  More actions

  November 26, 2017 at 12:44 am

  #1969448

  There may be a few thoughts to bear in mind here; the BETWEEN operator is inclusive and therefore may not give you the desired results, also there may be a need for the date time format of your [date] column although your test data does not indicate that.
  I think Thom was trying to get across a very valid point too; an alternative to Steve's suggestion might therefore be something like:

  
SELECT CAST([date] as date) [Date], [KW], [PP], [PM], [CP], [? T], FROM #test_table
WHERE [Date] >= '20161201' and [Date] < '20170206';

  

  ...