is there any difference between dates

• dastagiri16

  Hall of Fame

  Points: 3544

  More actions

  July 11, 2012 at 9:34 pm

  #261096

  hi,

  i have two statements as below

  select * From doctor_list where doctor_registered_date between '2012-05-01' and '2012-05-02'

  and

  select * From doctor_list where doctor_registered_date between '2012-05-01 00:00:00' and '2012-05-02 00:00:00'

  is there any difference between there results ?

  Thanks,

  Dastagiri

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 11, 2012 at 10:02 pm

  #1511854

  dastagiri16 (7/11/2012)

  ---

  hi,

  i have two statements as below

  select * From doctor_list where doctor_registered_date between '2012-05-01' and '2012-05-02'

  and

  select * From doctor_list where doctor_registered_date between '2012-05-01 00:00:00' and '2012-05-02 00:00:00'

  is there any difference between there results ?

  Thanks,

  Dastagiri

  Depends on the datatype of doctor_registered_date and the possibly the data stored.

• GSquared

  SSC Guru

  Points: 260824

  More actions

  July 12, 2012 at 10:42 am

  #1512181

  Considering implicit conversion will take place if the column datatype isn't DateTime, no, there isn't a real difference between the two, and they should get the same results.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• CapnHector

  SSCoach

  Points: 16143

  More actions

  July 12, 2012 at 1:15 pm

  #1512272

  depending on how much accuracy you want to imply in your code would be which one i would choose. they are both the same once the implicit conversion occurs but depending on the application it may be better to use '2012-05-12 00:00:00' to signify in your code you explicitly mean midnight.

  ---

  For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

  For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

  Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
  Jeff Moden's Cross tab and Pivots Part 1[/url]
  Jeff Moden's Cross tab and Pivots Part 2[/url]

• Kumara1011

  SSC-Addicted

  Points: 424

  More actions

  July 13, 2012 at 4:12 am

  #1512436

  its depends on the data type for that column.

  for example: If it is a datetime then it will not show much difference.

  or else if it is a nvarchar its shows u much difference.

• Jeff Moden

  SSC Guru

  Points: 1004362

  More actions

  July 13, 2012 at 7:22 am

  #1512522

  dastagiri16 (7/11/2012)

  ---

  hi,

  i have two statements as below

  select * From doctor_list where doctor_registered_date between '2012-05-01' and '2012-05-02'

  and

  select * From doctor_list where doctor_registered_date between '2012-05-01 00:00:00' and '2012-05-02 00:00:00'

  is there any difference between there results ?

  Thanks,

  Dastagiri

  There's no difference. They both allow you to return the wrong data depending on whether your stored dates have times on them or not.

  If the intent of the your code is to return ALL dates from the 1st to the 2nd, then to account for the possibility of a time coming into the data, you need to change the code to handle possible times. The general form for that is...

  WHERE SomeColumn >= @StartDate and SomeColumn < DATEADD(dd,1,@EndDate)

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