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: 246695

  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

  If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

• 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: 246695

  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.

  If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

• 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 Enthusiast

  Points: 189

  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: 246695

  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:

  If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

• TSQL Tryer

  SSCrazy Eights

  Points: 8869

  More actions

  January 16, 2015 at 2:19 am

  #1770666

  Cheers everyone.

• Jeff Moden

  SSC Guru

  Points: 1003863

  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)