Transact SQL – DateAdd and avoid overflow errors

Background

Transact SQL has adequate support for Date arithmetic exposed through dateadd, datediff, etc.

Interesting yesterday I ran into a stumbling block due to a relatively high value for the interval argument.

It graciously handles ints intervals.

But, choked on a bigint.

Error

Error Text

Msg 8115, Level 16, State 2, Line 14
Arithmetic overflow error converting expression to data type int.

Error Image

Workaround

Overview

  1. The workaround proposed by Michael Valentine Jones goes like this
    • Rather than try to add the interval at one time, add succesively
      • Add Days
        • Divide the original increment by 86400 if adding seconds
          • 86400
            • 24 Hours * 60 minutes * 60 seconds
      • Add Seconds
        • Get what remains
          • Get module of the 86400
        • Apply what remained

Code

[dbo].[udfn_dateAddMilliSecondAvoidOverflow]


use master
go

if object_id('dbo.udfn_dateAddMilliSecondAvoidOverflow') is null
begin

	exec
	(
		'
			create function dbo.udfn_dateAddMilliSecondAvoidOverflow()
			returns datetime
			as
			begin
				return 0
			end

		'
	)
end
go

alter function [dbo].[udfn_dateAddMilliSecondAvoidOverflow]
(
	  @increment bigint
	, @base		 datetime

)
returns datetime
as
begin

	return
		(
			dateadd
			(
				  MILLISECOND
				, @increment % (86400 * 1000 )
				, dateadd
					(
						  day
						, @increment / ( 86400 * 1000)
						, @base
					)


			)
		)

end

go

grant execute on [dbo].[udfn_dateAddMilliSecondAvoidOverflow] to [public]
go
	

Sample Data set

 

Credit Roll

Crediting Michael Valentine Jones
Link

 

 

Informatica – PowerCenter Express – Datatype – XML

Background

As I dig more into Informatica and MySQL, found out that we were not properly copying XML column from SQL Server into MySQL.

 

Table Structure

Here is our table structure.

MSSQL

Admin-CourseManagerUserLog-MSSQL

MySQL

Admin-CourseManagerUserLog-MYSQL

 

Informatica

Original Table Structure

Physical Data Object – Admin.CourseManagerUserLog

PhysicalDataObject

Explanation:

Later on, was cognizant that our XML Column, details, precision or size is 0

 

Mappings

Mappings

Explanation:

Later on, noticed that for the details column, the arrow is not full.

 

Mappings - Later

 

Run Mappings

Run the mapping.

And, things ran well.  Was not until later that I found that the details column on the destination, MySQL, was empty.

 

Create View on the Source

Here is a workaround.

In the view, add new columns to track the unsupported XML Column.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if object_id('[Admin].[CourseManagerUserLog_XMLColumnConverted]') is null
begin

	exec('create view [Admin].[CourseManagerUserLog_XMLColumnConverted] as select 1/0 as [shell] ')

end
go

alter view [Admin].[CourseManagerUserLog_XMLColumnConverted]
as

SELECT
		    [id]
		  , [username]
		  , [resourceID]
		  , [status]
		  , [message]
		  , [details]
		  , [detailsAsVarcharMax]
				= cast(	[details] as varchar(max))	
		  , [detailsAsVarchar]
				= cast(	[details] as varchar(8000))	
		  , [userID]
		  , [refID]
		  , [record_created]
		  , [appointmentID]

from   [Admin].[CourseManagerUserLog]
GO

GRANT SELECT ON [Admin].[CourseManagerUserLog_XMLColumnConverted] TO [public]

go

 

Informatica

Resource

Resource

Explanation

  1. details, XML Column, comes in xml.  But, size is 0
  2. detailsAsVarchar, varchar column, size is 8000 ( max size in MS SQL Server )
  3. detailsAsVarcharMax [varchar(max) ], size is 0, as well

 

Mappings

After AutoLink

After AutoLink ….

Mappings-After-Autolink

Those columns with matching names are Auto Linked.

 

After Column – detailsAsVarchar is linked to details

And, then we manually linked the columns whose names are not matched.

Here in:

  1. detailsAsVarchar (MSSQL) right-arrow details (MySQL)

Mappings - Later - Details - 0448PM