Select product depend upon date

• sidharthgs6

  SSC Veteran

  Points: 238

  More actions

  September 23, 2012 at 4:20 am

  #281994

  Hai,

  Product Date

  --------------------------------------------------------

  pd0 2012-08-11 18:45:55.780

  Pd1 2012-08-11 18:55:17.020

  pd2 2012-08-11 19:06:58.623

  pd3 2012-08-18 12:00:01.193

  pd4 2012-08-25 12:13:04.077

  pd5 2012-08-25 17:28:30.347

  pd6 2012-08-25 18:23:16.473

  pd7 2012-09-18 18:29:58.360

  I want select the product based on from date and to date.

  For Example

  I want the select the product date in between 2012-08-11 to 2012-08-18

  Note:dont check the time.

  I want the query for select product based on only date not depend upon time

• Dave Ballantyne

  SSC-Dedicated

  Points: 33667

  More actions

  September 23, 2012 at 4:42 am

  #1541156

  So...

  WHERE [Product Date] >='20120811' and [Product Date] <'20120819'

  will do you, you dont need to ignore the time, just adjust the search arguments to match your data

  ---

  
  Clear Sky SQL
  My Blog[/url]

• Paul White

  SSC Guru

  Points: 150468

  More actions

  September 23, 2012 at 6:02 am

  #1541159

  sidharthgs6, it is helpful to provide sample data using DDL statements as shown below:

  CREATE TABLE #Example

  (

  Product character(3) PRIMARY KEY,

  TheDate datetime2(3) NOT NULL

  );

  INSERT #Example

  (Product, TheDate)

  VALUES

  (CONVERT(character(3), 'pd0'), CONVERT(datetime2(3), '2012-08-11 18:45:55.780', 121)),

  (CONVERT(character(3), 'Pd1'), CONVERT(datetime2(3), '2012-08-11 18:55:17.020', 121)),

  (CONVERT(character(3), 'pd2'), CONVERT(datetime2(3), '2012-08-11 19:06:58.623', 121)),

  (CONVERT(character(3), 'pd3'), CONVERT(datetime2(3), '2012-08-18 12:00:01.193', 121)),

  (CONVERT(character(3), 'pd4'), CONVERT(datetime2(3), '2012-08-25 12:13:04.077', 121)),

  (CONVERT(character(3), 'pd5'), CONVERT(datetime2(3), '2012-08-25 17:28:30.347', 121)),

  (CONVERT(character(3), 'pd6'), CONVERT(datetime2(3), '2012-08-25 18:23:16.473', 121)),

  (CONVERT(character(3), 'pd7'), CONVERT(datetime2(3), '2012-09-18 18:29:58.360', 121));

  -- Demo index

  CREATE INDEX nc1 ON #Example (TheDate);

  -- Dave's solution

  SELECT

  e.Product,

  e.TheDate

  FROM #Example AS e

  WHERE

  e.TheDate >= CONVERT(datetime2(3), '20120811', 112)

  AND e.TheDate < CONVERT(datetime2(3), '20120819', 112);

  -- Also works, but not as efficient

  SELECT

  e.Product,

  e.TheDate

  FROM #Example AS e

  WHERE

  CONVERT(date, e.TheDate)

  BETWEEN

  CONVERT(date, '2012-08-11', 121)

  AND CONVERT(date, '2012-08-18', 121);

  Paul White
  All articles available on SQL.kiwi
  @SQL_Kiwi

• sidharthgs6

  SSC Veteran

  Points: 238

  More actions

  September 24, 2012 at 12:08 am

  #1541273

  Hai Dave Ballantyne,

  Thanks your reply got my solution