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: 1000539 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)
Intro to Tally Tables and Functions

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

sqlpro

Hall of Fame

Points: 3859

April 29, 2004 at 9:39 pm

#504763

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: 4783 More actions · Answer 3

Razvan Socol

SSCarpal Tunnel

Points: 4783

April 30, 2004 at 12:45 am

#504773

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