how to write the sql?

Question

4.00/5 (1 vote)

See more:

Hi,

I wrote sql as below,

SQL

select * from STS_VEHICLE_RUNNING_STATUS
WHERE VRS_DEVICEID='ST0001' AND VRS_UTCTIME BETWEEN '7/2/2012' AND '10/9/2012'

It retrieve values.But exclude the values for 10/9/2012.

If I have a table with a StartDate column and an EndDate column can I produce a query that returns a set including every day in the range. I could use a table variable and do some procedural code but I'd like to know if there's a way to do it in a query.

E.g. StartDate = 1/1/2010, EndDate = 1/5/2010, result would be:

•1/1/2010
•1/2/2010
•1/3/2010
•1/4/2010
•1/5/2010
...for every row in the table that has the StartDate and EndDate columns.

plz help...

Thanks..

Posted 15-Nov-12 22:13pm

hasbina

Updated 15-Nov-12 22:54pm

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MT_ · Answer 1 · 2012-11-15T23:12:00

Solution 2

You can use simpler query I guess,

SQL

SELECT *
FROM STS_VEHICLE_RUNNING_STATUS
WHERE
CONVERT(VARCHAR(10), VRS_UTCTIME ,101) >= CONVERT(VARCHAR(10) ,@YourDate,101)
AND CONVERT(VARCHAR(10) ,VRS_UTCTIME ,101) <= CONVERT(VARCHAR(10) ,@YourDate,101)
AND VRS_DEVICEID='ST0001'

This should include the dates.

Milind

Posted 15-Nov-12 23:12pm

MT_

Updated 15-Nov-12 23:21pm

v2

Comments

hasbina 16-Nov-12 5:20am

hi milind,

execute query using

SELECT *
FROM STS_VEHICLE_RUNNING_STATUS
WHERE
CONVERT(VARCHAR(10) VRS_UTCTIME ,101) >= CONVERT(VARCHAR(10) ,'7/2/2012',101)
AND CONVERT(VARCHAR(10) ,VRS_UTCTIME ,101) <= CONVERT(VARCHAR(10) ,'10/9/2012',101)
AND VRS_DEVICEID='ST0001'

it gave error as,

Incorrect syntax near 'VRS_UTCTIME'.

why?

MT_ 16-Nov-12 5:22am

a comma was missed before VRS_UTCTIME. Updated query in the solution.

If it helps, mark it as answe/upvote
Milind

hasbina 16-Nov-12 5:30am

hi milind,

query result retrieve zero record

MT_ 16-Nov-12 5:37am

I am unable to make out date format from 7/2/2012. If it dd/mm/yy format then replace 101 by 103 in my query..

hasbina 16-Nov-12 5:43am

hi milind,
7/2/2012 in mm/dd/yy format
hasbina

MT_ 16-Nov-12 5:46am

Sorry if it is too obvious, but are there record for ST0001 between 2-Sept-2012 and 9-Oct-2012?

Varun_Kumar · Answer 2 · 2012-11-15T22:17:00

Solution 1

Write

C#

'11/9/2012'

instead of

C#

'10/9/2012'

This is because time component is taken as 00:00:00(ie starting of time).

or

You replace by

C#

'10/9/2012 12:00:00 PM'

Posted 15-Nov-12 22:17pm

Varun_Kumar

Comments

Abhishek Pant 16-Nov-12 4:23am

What happens if the user is using date as a datatype.now you will say change the datatype!!

hasbina 16-Nov-12 4:23am

Hi varun_kumar,
No.It is because of BETWEEN operator selects fields between the test values, including the first test value and excluding the last test value.

My doubt is have any other operator instead of between?

Varun_Kumar 16-Nov-12 4:47am

Then you can use

'7/2/2012' < VRS_UTCTIME <= '10/9/2012'

hasbina 16-Nov-12 4:52am

@varun
no.It also provide same result, excluding date 10/9/2012

Abhishek Pant 16-Nov-12 5:13am

your query is correct but do you know about date format as it is used as mm:dd:yyyy in queries.

hasbina 16-Nov-12 5:16am

yes.its in mm:dd:yyyy format

Abhishek Pant 16-Nov-12 5:50am

see this http://www.codeproject.com/Answers/488491/Sqlplusserverplus2005plusbetweenplusclausepluswith#answer2