Sql select query working abnormally

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

I am confused by the nature of logical operators upon datetime data in sql.

In my database's table RecordDateTime is saved like:

2014-04-09 15:28:32.000

I am retrieving on basis of this query:

SQL

select * FROM [GPSDB].[dbo].[Coordinates] where IMEI= '354400055967495' and RecordedDateTime >= '2014-04-09 15:28:32.000' and RecordedDateTime <= '2014-04-09 15:39:20.000'

Now it returns 4 rows which falls between these both dates range or equal as expected but confusion starts from here when i remove Seconds part from first date then still it works and returns same result set i.e.

SQL

select * FROM [GPSDB].[dbo].[Coordinates] where IMEI= '354400055967495'and RecordedDateTime >= '2014-04-09 15:28' and RecordedDateTime <= '2014-04-09 15:39:20.000;

but if i remove seconds part from 2nd date then it returns only one row with matching time of first date being provided. Weird ? :/

Help Please.

Posted 15-Apr-14 21:03pm

Hunain Hafeez

Updated 15-Apr-14 21:40pm

Herman<T>.Instance

v2

Add a Solution

Comments

syed shanu 16-Apr-14 3:18am

So what is your problem in this.What you want exactly.Do you want to select all all data with in Date range or do you want to select the data with in Date and Time range or do you want to select the data with in Date Time and with Seconds.can you paste you first query 4 record output mainly paste the RecordedDateTime result.

1 solution

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)

Aarti Meswania · Accepted Answer · 2014-04-15T22:08:00

this is second date '2014-04-09 15:39:20.000'
if you write '2014-04-09 15:39' then it will be take seconds 00.000 by default so...

SQL

'2014-04-09 15:39' = '2014-04-09 15:39:00.000'

so, the records having RecordedDateTime greater than '2014-04-09 15:39:00.000' will not be displayed
because '2014-04-09 15:39:20.000' is not less than or equal to '2014-04-09 15:39:00.000'
Happy Coding!
:)