CLR: arithmetic overflow while insert into a table containing a datetime column

problem: you get an arithmetic overflow error while you want in your CLR stored procedure to insert into a table containing a datetime column
reason: datetime-format on sql serveris set up differently than you use it
workaround:  you can use SET DATEFORMAT before you call you CLR stored procedures.

example:

USE Database
GO

DECLARE    @iReturnvalue int
–set datetime format you want
SET DATEFORMAT ymd;
GO

— call CLR stored procedure
EXEC   [dbo].[sp] @iMandant = 1, @iReturnvalue = @iReturnvalue OUTPUT

Advertisements

vba: convert a datetime-string yyyymmddhhmmss to date

Public Function Conv2Datetime(sDatetimestamp As String) As Date
‘Format of sDatetimestamp:= yyyymmddhhmmss

Dim dtm As Date
Dim dS As Date
Dim dT As Date

dS = DateSerial(CInt(Left(sDatetimestamp, 4)), CInt(Mid(sDatetimestamp, 5, 2)), CInt(Mid(sDatetimestamp, 7, 2)))
dT = TimeSerial(CInt(Mid(sDatetimestamp, 9, 2)), CInt(Mid(sDatetimestamp, 11, 2)), CInt(Mid(sDatetimestamp, 13, 2)))
dtm = CDate(Str(dS) + ” ” + Str(dT))

Conv2Datetime = dtm

End Function

Public Function Conv2Date(sDatetimestamp As String) As Date
‘Format of sDatetimestamp:= yyyymmddhhmmss

Dim dS As Date

dS = DateSerial(CInt(Left(sDatetimestamp, 4)), CInt(Mid(sDatetimestamp, 5, 2)), CInt(Mid(sDatetimestamp, 7, 2)))

Conv2Date = dS

End Function

Public Function Conv2Time(sDatetimestamp As String) As Date
‘Format of sDatetimestamp:= yyyymmddhhmmss

Dim dT As Date

dT = TimeSerial(CInt(Mid(sDatetimestamp, 9, 2)), CInt(Mid(sDatetimestamp, 11, 2)), CInt(Mid(sDatetimestamp, 13, 2)))

Conv2Time = dT

End Function