Datetime issue while selecting

• Gangadhara MS

  Hall of Fame

  Points: 3377

  More actions

  June 3, 2011 at 2:28 am

  #240782

  Dear All,

  I have a table with 5 column out of which one column datatyp is "Datetime"

  ID,Country,Dateruled Isactive

  Ex: 1,India,Rural,2008-05-15 18:30:00.000 ACTIVE

  The datatype for the Dateruled column is of Datetime.

  When i query a table using

  select * from <tablename> where Dateruled='2008-05-15'

  will not result any result set even though we have records.But when i use

  select * from <tablename> where Dateruled>='2008-05-15'

  It shows all the records above that date.

  Could somebody help me to how do we fetch the records for that date.

  Thanks,

  Ganga

• Phil Parkin

  SSC Guru

  Points: 247124

  More actions

  June 3, 2011 at 2:44 am

  #1333547

  Gangadhara MS (6/3/2011)

  ---

  Dear All,

  I have a table with 5 column out of which one column datatyp is "Datetime"

  ID,Country,Dateruled Isactive

  Ex: 1,India,Rural,2008-05-15 18:30:00.000 ACTIVE

  The datatype for the Dateruled column is of Datetime.

  When i query a table using

  select * from <tablename> where Dateruled='2008-05-15'

  will not result any result set even though we have records.But when i use

  select * from <tablename> where Dateruled>='2008-05-15'

  It shows all the records above that date.

  Could somebody help me to how do we fetch the records for that date.

  Thanks,

  Ganga

  The likely problem is that your dates also include a time component, which is throwing out your selection. To ignore that time component, you need to refine your query a little (untested):

  SELECT *

  FROM tablename

  WHERE CAST(DATEDIFF(dd, 0, Dateruled) AS DATETIME) = '2008-05-15'

  ---

• Johan Bijnens

  SSC Guru

  Points: 135450

  More actions

  June 3, 2011 at 2:46 am

  #1333549

  keep in mind datetime holds date and time.

  Your predicate looks for yourdatecol="2011-06-02 00:00:00.000'

  edited.

  Oh please don't use a date conversion function because it will avoid an index being used in the most optimal way !!

  Write the query like:

  Where yourdatecol >= "2011-06-02 00:00:00.000'

  and yourdatecol <'"2011-06-03 00:00:00.000'

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me