Date/time question

• Vikka

  Hall of Fame

  Points: 3715

  More actions

  January 2, 2008 at 3:28 pm

  #93140

  How to get date part of the dateTime without converting it into a string because when I order by such string I do not get correct order if dates are for different years..

  If I have:

  2007-12-30 09:36:09.890

  I need 2007-12-30 date

  but I don't want to convert it into a string

  CONVERT( char(10), getDate(), 101)

  because I will get date as a string '01/02/2008'

  and if I order by date I will get

  01/02/2008

  12/31/2007

  though it suppose to be

  12/31/2007

  01/02/2008

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  January 2, 2008 at 3:42 pm

  #764204

  You could still convert to a string, use format code 112 instead of 101. It will give your date in yyyymmdd format, and will sort as you expect.

  This will strip the time portion off a datetime value: dateadd(dd,datediff(dd,0,getdate()), 0)

  😎

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  January 2, 2008 at 3:44 pm

  #764205

  This is probably the easiest way to do it:

  select dateadd(day,datediff(day,0,'2007-12-30 09:36:09.890'),0)

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  January 2, 2008 at 11:56 pm

  #764269

  Concur... just about the fastest way, too!

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