DATE cONVERT

• TSQL Tryer

  SSCrazy Eights

  Points: 8869

  More actions

  January 15, 2015 at 9:41 am

  #293806

  I have a date filed - an example of what it pulls back is -

  2013-05-10 08:15:24.000

  I want it to just return - 2013-05-10 00:00:00.000, so the time is ignored altogether.

  What do I need in my select if my select is similar to below -

  Select datefield

  from datetable

  Thanks

• Phil Parkin

  SSC Guru

  Points: 246986

  More actions

  January 15, 2015 at 9:45 am

  #1770505

  TSQL Tryer (1/15/2015)

  ---

  I have a date filed - an example of what it pulls back is -

  2013-05-10 08:15:24.000

  I want it to just return - 2013-05-10 00:00:00.000, so the time is ignored altogether.

  What do I need in my select if my select is similar to below -

  Select datefield

  from datetable

  Thanks

  Select datefield = cast(datefield as date)

  from datetable

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• imex

  Ten Centuries

  Points: 1234

  More actions

  January 15, 2015 at 9:48 am

  #1770506

  Try:

  Select dateadd(day, datediff(day, 0, datefield), 0) as datefield

  from datetable

  Hope this helps.

  https://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos

• Alvin Ramard

  SSC-Forever

  Points: 41190

  More actions

  January 15, 2015 at 10:06 am

  #1770511

  imex (1/15/2015)

  ---

  Try:

  Select dateadd(day, datediff(day, 0, datefield), 0) as datefield

  from datetable

  Hope this helps.

  This is a waste of CPU cycles.

  Stick with Phil Parkins suggestion. Stick to the KISS principle when possible.

  ---

  
  Alvin Ramard
  Memphis PASS Chapter[/url]

  All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

  For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

• Phil Parkin

  SSC Guru

  Points: 246986

  More actions

  January 15, 2015 at 10:09 am

  #1770512

  Try:

  Select dateadd(day, datediff(day, 0, datefield), 0) as datefield

  from datetable

  Hope this helps.

  http://www.imoveisemexposicao.com.br[/quote%5D

  Same execution plan for my solution, though yours does return the zero time component & is therefore a more exact match to requirements 😀

  Edit: Oops, quoted wrong post on original reply.

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  January 15, 2015 at 10:17 am

  #1770515

  Phil Parkin (1/15/2015)

  ---

  Try:

  Select dateadd(day, datediff(day, 0, datefield), 0) as datefield

  from datetable

  Hope this helps.

  http://www.imoveisemexposicao.com.br[/quote%5D

  Same execution plan for my solution, though yours does return the zero time component & is therefore a more exact match to requirements 😀

  Edit: Oops, quoted wrong post on original reply.

  As this is in a SQL Server 2012 forum, we can make an assumption that the OP is using SQL Server 2012. With that, Phil, your solution is quite valid. If you assign the result of your solution to a datetime variable or column, it will be converted back to a datetime value with a zero time component. This solution will also wotk in SQL Server 2008. The dateadd solution is valid for SQL Server 2005 and older.

• Prudhvi _SQL

  SSC Veteran

  Points: 209

  More actions

  January 15, 2015 at 12:09 pm

  #1770534

  SELECT CAST(CAST(datefield AS DATE) AS DATETIME)

  FROM datetable

• Phil Parkin

  SSC Guru

  Points: 246986

  More actions

  January 15, 2015 at 12:24 pm

  #1770541

  Prudhvi _SQL (1/15/2015)

  ---

  SELECT CAST(CAST(datefield AS DATE) AS DATETIME)

  FROM datetable

  Why stop there?

  How about

  select cast((stuff(stuff(convert(char(8), datefield, 112), 5, 0, '-'), 8, 0, '-')) as datetime)

  ?:hehe:

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• TSQL Tryer

  SSCrazy Eights

  Points: 8869

  More actions

  January 16, 2015 at 2:19 am

  #1770666

  Cheers everyone.

• Jeff Moden

  SSC Guru

  Points: 1004407

  More actions

  January 16, 2015 at 8:48 am

  #1770780

  TSQL Tryer (1/15/2015)

  ---

  I have a date filed - an example of what it pulls back is -

  2013-05-10 08:15:24.000

  I want it to just return - 2013-05-10 00:00:00.000, so the time is ignored altogether.

  What do I need in my select if my select is similar to below -

  Select datefield

  from datetable

  Thanks

  I see a potential SARGability problem that will kill performance here. Could you post the actual query that you're trying to build?

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