Add to DATE hour - TSQL-CodePudding

I have a column type time(7).

What i want is to add in time column to date.

i manage to get the only date using GETDATE() function but i fail to add the time part next to date.

Query:

SELECT [Compay]
      ,[Time]
      ,CAST(GETDATE() AS DATE) AS Today
      ,CAST(CAST(GETDATE() AS DATE) AS NVARCHAR) AS Today_AS_Nvarchar
      ,CAST([Time] AS NVARCHAR) AS Time_AS_Nvarchar
      ,CAST(CAST(GETDATE() AS DATE) AS NVARCHAR)   ' '   CAST([Time] AS NVARCHAR) AS Today_Time_AS_Nvarchar
      ,CONVERT(datetime,CAST(CAST(GETDATE() AS DATE) AS NVARCHAR)   ' '   CAST([Time] AS NVARCHAR),103)
  FROM [Testing_Env].[dbo].[Com_CD_Test]

Error:

Conversion failed when converting date and/or time from character string.

The error arise on CONVERT(datetime,CAST(CAST(GETDATE() AS DATE) AS NVARCHAR) ' ' CAST([Time] AS NVARCHAR),103)

is there any easier/orthodox way to achieve it?

CodePudding user response：

You can't add the new date and time data types together like you can the old data types; personally I think this is also better as it stop people treating dates and times like a numerical value.

Assuming you have a date column and a time column you have a few of options. The first is to CAST/CONVERT both to a datetime and then "add" them together. Because the "old" data types work more like numerical values this works, however, you will lose accuracy of your time value if it has a precision of 3 or higher (as datetime is only accurate to 1/300 seconds):

DECLARE @TimeValue time(7) = '17:52:12.1234567',
        @DateValue date = '20211016';
SELECT CONVERT(datetime, @DateValue)   CONVERT(datetime, @TimeValue);

If loosing accuracy isn't an option, then you could to use conversion on the date value and use DATEDIFF and DATEADD. For a time(7) you'll want to be using nanoseconds (as microseconds isn't accurate enough). Unfortunately this poses another problem; DATEADD can't handle bigint values (still) and there is no DATEADD_BIG (like there is DATEDIFF_BIG), so this becomes overly complex. You need to first get the difference in milliseconds, and then add the remainder in nanoseconds to still be accurate to 1/1000000 of a second:

DECLARE @TimeValue time(7) = '17:52:12.1234567',
        @DateValue date = '20211016';
SELECT DATEADD(NANOSECOND,DATEDIFF_BIG(NANOSECOND,'00:00:00', @TimeValue) % 1000000,DATEADD(MILLISECOND, DATEDIFF_BIG(MILLISECOND,'00:00:00', @TimeValue), CONVERT(datetime2(7),@DateValue)));

Finally, yes, you can convert to values to strings, and then to a datetime2 value; this is probably the easiest methiod. You just need to ensure you use style codes:

DECLARE @TimeValue time(7) = '17:52:12.1234567',
        @DateValue date = '20211016';
SELECT CONVERT(datetime2(7),CONVERT(varchar(10), @DateValue, 23)   'T'   CONVERT(varchar(17), @TimeValue, 114),126);