How does Convert function work

• sowmya.boodaderureddappa

  Grasshopper

  Points: 16

  More actions

  September 9, 2011 at 12:49 am

  #247532

  Hi All

  We are using a convert function for date column in where condition and doing comparison. BUt its not working as it has to

  ( Report date >= Effective start date )

  '2010-02-09 00:00:00' >= '2011-31-08 00:00:00' after conversion it becomes (9/2/2010 >= 8/31/2011 ) . The type used is 101 in convert function. But the same condition is working fine for other types like 102,103 .

  this condition should be failed as report date is less than effective start date however the condition is passing because of the use of date type convert function.

  Please let me know why when using convert function for 101 the behaviour is different.

  Cheers

  Sowmya

• Jeff Moden

  SSC Guru

  Points: 1004592

  More actions

  September 9, 2011 at 1:33 am

  #1379041

  sowmya.boodaderureddappa (9/9/2011)

  ---

  Hi All

  We are using a convert function for date column in where condition and doing comparison. BUt its not working as it has to

  ( Report date >= Effective start date )

  '2010-02-09 00:00:00' >= '2011-31-08 00:00:00' after conversion it becomes (9/2/2010 >= 8/31/2011 ) . The type used is 101 in convert function. But the same condition is working fine for other types like 102,103 .

  this condition should be failed as report date is less than effective start date however the condition is passing because of the use of date type convert function.

  Please let me know why when using convert function for 101 the behaviour is different.

  Cheers

  Sowmya

  The reason why is because you're now sorting characters instead of dates. Don't use CONVERT for anything expect for, maybe, a final output to a file. Other than that, you should let the app or the report generator format the date for fnal display.

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