date range query

Question

Post reply

date range query

sqlpro

Hall of Fame

Points: 3859
More actions
April 29, 2004 at 8:56 pm

#107978

HI FRIENDS
i've data like following
supportid,fk_clientid,cl_name,fk_staffid,Staffname,fk_catid,Catname,supp_startdt,supp_stopdt
1,,,RK,rajani,6,BUG,2004-03-07 14:54:19.000,2004-03-07 14:54:36.000
2,DI134,Alana 1340,RK,rajani,2,ALT F8,2004-03-07 15:06:55.000,2004-03-07 15:32:16.000
2,DI137,Alana 1370,RK,rajani,2,ALT F8,2004-03-07 15:06:55.000,2004-03-07 15:32:16.000
3,C2833,Derek 2833,RK,rajani,4,AUDIT,2004-03-07 16:13:54.000,2004-03-07 16:14:17.000
4,,,RK,rajani,3,APT BOOK,2004-03-19 12:33:48.000,2004-03-19 12:37:30.000
5,,,RK,rajani,3,APT BOOK,2004-03-19 12:37:30.000,2004-03-19 12:40:40.000
6,,,RK,rajani,3,APT BOOK,2004-03-19 12:40:40.000,2004-03-19 12:43:28.000
7,,,RK,rajani,2,ALT F8,2004-03-19 12:43:32.000,2004-03-19 12:43:36.000
8,,,RK,rajani,2,ALT F8,2004-03-23 16:41:56.000,2004-03-23 16:43:43.000
when i run following query
SELECT * FROM DBO.pt_vCheckSupport
WHERE supp_startdt>='07-MAR-2004' AND supp_startdt<='19-MAR-2004'
it excluding records dated 19-mar-2004 which i expect to be included
how can i change my query so that it returns data based on date range i specified.
Thanks for ur ideas

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 1

SELECT * FROM DBO.pt_vCheckSupport

WHERE supp_startdt>='07-MAR-2004' AND supp_startdt<'20-MAR-2004'

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqlpro Hall of Fame Points: 3859 More actions · Answer 2

Thanks Jeff

i finally did like

SELECT * FROM DBO.pt_vCheckSupport

WHERE CAST(CONVERT(VARCHAR(11), DBO.pt_vCheckSupport.supp_startdt, 101) AS DATETIME)>= '07-MAR-2004'

AND CAST(CONVERT(VARCHAR(11), supp_startdt, 101) AS DATETIME)<='19-MAR-2004'

Razvan Socol SSCarpal Tunnel Points: 4811 More actions · Answer 3

You should know that using CONVERT will prevent SQL Server to use any indexes. If the table is large and you want to use an index on the supp_startdt, you should use the method that Jeff suggested.

Razvan

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 4

I would also reccomend not to use the month-name style for the arguments. For it to be parsed correctly at all times, it's dependant on external settings and language.

(ie if the server can't parse the name of the month, it will throw an error)

so, instead of;

WHERE supp_startdt >='07-MAR-2004' AND supp_startdt='20040307' AND supp_startdt<'20040320'

..and no, even if your result from a query like;

SELECT supp_startdt

FROM ....

should return

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

'07-MAR-2004'

this is just the clients chosen display style - it is not the actual value stored in the column, so '20040307' works equally well - providing that the column is datetime datatype.

... just want to plant some seeds on using the only non-ambigous dateformat - yyyymmdd (style 112)

=;o)

/Kenneth

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Also staying away from using CONVERT should perform slightly better, as SQL Server does not need to query the syslanguages system table.

Every litlle bit helps

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

sqlpro Hall of Fame Points: 3859 More actions · Answer 6

Thank u very much friends

i'll change my query

Rajani