Select product depend upon date

Question

Post reply

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

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 · Answer 2

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 · Answer 3

Hai Dave Ballantyne,

Thanks your reply got my solution