Retrieve Time from DateTime (2005)

• kuopaz

  Default port

  Points: 1449

  More actions

  October 28, 2011 at 4:17 am

  #390712

  I'm after a way to strip out the date from a smalldatetime column, but still store it as smalldatetime. Using SQL Server 2005 so no Time data type to cast to.

  declare @OrigDT as smalldatetime

  declare @NewT as smalldatetime

  select @OrigDT = '10/28/2011 11:10'

  select @NewT --to return '01/01/1900 11:10'

  For building a calendar table, must be an easy way to do it...

  Thanks.

• kuopaz

  Default port

  Points: 1449

  More actions

  October 28, 2011 at 4:54 am

  #1400554

  select @NewT = dateadd(day, -1 * datediff(day, '01/01/1900', @OrigDT), @OrigDT)

  Seems to do it?

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  October 28, 2011 at 5:15 am

  #1400560

  SELECT DATEADD(dd, DATEDIFF(dd, 0, @OrigDT), 0)

  --EDIT-- Mis-read your post, the above removes the time from a datetime. Should have been like the below

  SELECT DATEADD(dd, 0-DATEDIFF(dd, 0, @OrigDT), @OrigDT)

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---