September 23, 2012 at 4:20 am
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
September 23, 2012 at 4:42 am
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
September 23, 2012 at 6:02 am
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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 24, 2012 at 12:08 am
Hai Dave Ballantyne,
Thanks your reply got my solution
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply