Manipulating time datatype

• Chrissy321

  SSCoach

  Points: 15763

  More actions

  January 3, 2012 at 4:07 pm

  #262347

  Hello All,

  I am trying to use the time data type in a scheduling app.

  DECLARE @InitiationDateTime datetime

  SET @InitiationDateTime = getdate()

  DECLARE @SheduledTime time(7)

  SET @SheduledTime = '22:00:00.0000000'

  How can I combine these to get '2012-01-03 22:00:00'

  Do I need to perform string manuipulation or are there any built in functions to do this?

  More theoretically, what are the advantages of the new time datatype?

  I assume I could perform string manipulation on the following as well.

  DECLARE @InitiationDateTime datetime

  SET @InitiationDateTime = getdate()

  DECLARE @SheduledTime datetime

  SET @SheduledTime = '2000-01-03 22:00:00'

  Thanks so much...

• andersg98

  SSCertifiable

  Points: 5180

  More actions

  January 3, 2012 at 5:12 pm

  #1428044

  This works for me:

  SELECT CAST(CAST(@InitiationDateTime AS DATE) AS DATETIME) + @SheduledTime

• Paul White

  SSC Guru

  Points: 150467

  More actions

  January 3, 2012 at 10:07 pm

  #1428078

  Chrissy321 (1/3/2012)

  ---

  DECLARE @InitiationDateTime datetime

  SET @InitiationDateTime = getdate()

  DECLARE @SheduledTime time(7)

  SET @SheduledTime = '22:00:00.0000000'

  It seems odd, on the face of it, to use the older DATETIME data type (with its millisecond resolution, rounded to increments of .000, 0.003, 0.007 seconds) with TIME(7), which has seven digits of fractional second precision. The other thing that strikes me is that you are looking to overwrite the time portion of the DATETIME with the TIME(7) data.

  How can I combine these to get '2012-01-03 22:00:00'. Do I need to perform string manuipulation or are there any built in functions to do this? More theoretically, what are the advantages of the new time datatype?

  Generally, it seems the DATE and TIME types are intended to be used entirely separately, where the smaller storage size can be very useful; there is precious little support for combining them directly, so yes, you would probably end up doing conversions to and from strings. For example:

  DECLARE

  @d DATE = SYSDATETIME(), -- date only

  @t TIME = SYSDATETIME() -- time only

  -- Combine to DATETIME2 without losing precision

  DECLARE

  @dt DATETIME2(7) =

  CONVERT(DATETIME2,

  CONVERT(CHAR(10), @d, 126) + 'T' + CONVERT(CHAR(16), @t, 126), 126)

  SELECT

  @d AS [date],

  @t AS [time],

  @dt AS [date_time]

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi

• Chrissy321

  SSCoach

  Points: 15763

  More actions

  January 4, 2012 at 9:14 am

  #1428413

  >>It seems odd, on the face of it,

  Yes its true, I'm somewhat in explaratory mode here...

  If the predominant advantage of using TIME is the smaller storage size and the precision then its probably not applicable to my situation. My data sets are quite small and milleseconds should be sufficient.

  >>SELECT CAST(CAST(@InitiationDateTime AS DATE) AS DATETIME) + @SheduledTime

  This does work quite nicely. I may end up using this or I may simple use two datetimes fields like this.

  DECLARE @InitiationDateTime datetime

  SET @InitiationDateTime = getdate()

  PRINT @InitiationDateTime

  DECLARE @SheduledTime datetime

  SET @SheduledTime = '2000-01-01 22:00:00'

  PRINT @SheduledTime

  SELECT CAST(DATEADD(dd,0,DATEDIFF(dd,0,@InitiationDateTime)) + ' ' +DATEADD(Day, -DATEDIFF(Day, 0, @SheduledTime), @SheduledTime) as datetime)