Not Displaying Records of one Date

Question

Not Displaying Records of one Date

jai_anand2004

Newbie

Points: 2
More actions
December 13, 2008 at 8:51 am

#383567

Hi all,
i am Jayateertha from Mysore India, I have a Problem in sql server 2005 while i was running a sql query
Select Name,City,registeredDate from tblMembers where RegisteredDate >= '12/09/2008' and RegisteredDate<= '12/12/2008'
it should display all the Name,city and RegisteredDate inclusive of Both the dates but it is not displaying the records of Last date (12/12/2008)

Viewing 9 posts - 1 through 8 (of 8 total)

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

george sibbald SSC Guru Points: 104210 More actions · Answer 1

jai_anand2004 (12/13/2008)
Hi all,
i am Jayateertha from Mysore India, I have a Problem in sql server 2005 while i was running a sql query
Select Name,City,registeredDate from tblMembers where RegisteredDate >= '12/09/2008' and RegisteredDate<= '12/12/2008'
it should display all the Name,city and RegisteredDate inclusive of Both the dates but it is not displaying the records of Last date (12/12/2008)

thats because if you don't specify minutes and seconds they default to 00:00:00. so change query to < '13/12/2008'

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

ps. One Orange Chip Points: 29423 More actions · Answer 2

the datetime data type stores both date and time.

so the result of

RegisteredDate<= '12/12/2008'

will output all dates less than on equal to 2008-12-12 00:00:00.000

if the record has a RegisteredDate colums's value as 2008-12-12 10:30:00.000, it wont be fetched using the condition you've written.

To overcome this either change the query to

RegisteredDate< '12/13/2008'

or RegisteredDate <='2008-12-12 23:59:59.997'

Pradeep Singh

george sibbald SSC Guru Points: 104210 More actions · Answer 3

sorry didn't know settings on your server. if format is mm/dd/yyyy then yes use

RegisteredDate< '12/13/2008'

so my syntax would have errored for you.

I come from a sensible part of the world where days come before months 🙂

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

ps. One Orange Chip Points: 29423 More actions · Answer 4

well i too presumed the format was mmddyyyy..

he can still twick the query even ih he doesnt know the server's date format by executing either set dateformat 'dmy' OR set dateformat 'mdy' before running his query.... 😉

Pradeep Singh

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 5

So, Jayateertha from Mysore India... did any of those answer your question?

--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)

ps. One Orange Chip Points: 29423 More actions · Answer 6

Seems he forgot to come back to see the responses!!

His visits count still is 1.

Pradeep Singh

jai_anand2004 Newbie Points: 2 More actions · Answer 7

jai_anand2004

Newbie

Points: 2

December 16, 2008 at 3:16 am

#913011

Thanks all ... surely it worked.

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 8

Cool... thanks for the feedback.

--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)