Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Select product depend upon date Expand / Collapse
Author
Message
Posted Sunday, September 23, 2012 4:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 29, 2013 9:22 PM
Points: 6, Visits: 14
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
Post #1363213
Posted Sunday, September 23, 2012 4:42 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:06 AM
Points: 1,768, Visits: 8,318
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
Kent user group
Post #1363214
Posted Sunday, September 23, 2012 6:02 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, November 20, 2014 7:53 PM
Points: 9,928, Visits: 11,194
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1363217
Posted Monday, September 24, 2012 12:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 29, 2013 9:22 PM
Points: 6, Visits: 14
Hai Dave Ballantyne,

Thanks your reply got my solution
Post #1363340
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse