Problem with Compairing dates RSS

• • Reply
  

  Clubict

  Member

  223 Points

  195 Posts

  Problem with Compairing dates

  Jan 24, 2013 10:02 AM|LINK

  Hi,

  I've a question with date compaire in sql.

  In my believes < is smaller and > is bigger

  But when i run the statement below it doesn`t return true..

  Correct me if I'm wrong, but isn`t the date of today bigger than the date of last year?

  Best regards,

  Mark

  Select 'true'

  where CONVERT(VARCHAR(8),GETDATE(),101) > CONVERT(VARCHAR(8),'12/31/2012',101)

• • Reply
  

  AidyF

  Star

  9246 Points

  1576 Posts

  Re: Problem with Compairing dates

  Jan 24, 2013 10:07 AM|LINK

  You are converting to varchar so are comparing the strings alphabetically rather than treating them as dates

  Select 'true'
   
  where CONVERT(datetime,GETDATE(),101) > CONVERT(datetime,'12/31/2012',101)

  • Marked as answer by clubict on Jan 24, 2013 10:38 AM
• • Reply
  

  anuj_koundal

  Contributor

  2215 Points

  529 Posts

  Re: Problem with Compairing dates

  Jan 24, 2013 10:11 AM|LINK

  Select 'true'where GETDATE() > '12/31/2012'

  Regards
  Anuj Koundal
  My Blog
  Mark as Answer on the post that helps you.
• • Reply
  

  DarrellNorto...

  All-Star

  87405 Points

  9714 Posts

  Moderator

  MVP

  Re: Problem with Compairing dates

  Jan 24, 2013 10:13 AM|LINK

  Why are you converting to a string instead of comparing dates?

  This returns true:

  Select 'true' where GetDate() > '12/31/2012'

  
  

  Darrell Norton, MVP
  Darrell Norton's Blog
  Please click "Mark as Answer" if this helped you.
• • Reply
  

  asp.netforum...

  Member

  604 Points

  132 Posts

  Re: Problem with Compairing dates

  Jan 24, 2013 10:28 AM|LINK

  hai

  try it

  SELECT 'true'
  FROM tablename
  WHERE
  -- Compare year
  DATEPART(yyyy, GETDATE()) = 2012
  -- compare month
  AND DATEPART(mm, GETDATE()) = 31
  -- compare day
  AND DATEPART(dd, GETDATE()) = 12

  
  
  

  asp.net forums (Ramesh Rajendran)
• • Reply
  

  Clubict

  Member

  223 Points

  195 Posts

  Re: Problem with Compairing dates

  Jan 24, 2013 10:38 AM|LINK

  So what ypu are saying is that i need to convert it back to date?

  It has done the trick!..Thanks!

  Select 'true'

  where CONVERT(DATE, (CONVERT(VARCHAR(10),GETDATE(),101))) > CONVERT(DATE, (CONVERT(VARCHAR(10),'1/23/2013 12:00:00.001',101)))

  • Marked as answer by clubict on Jan 24, 2013 10:38 AM