CkDateTime SQL Server Reference Documentation
CkDateTime
Current Version: 11.6.0
CkDateTime represents a date/time value and provides conversion methods for common date/time formats, including .NET DateTime, DateTime ticks, RFC 822, RFC 3339 / ISO 8601-style timestamps, Unix time, DOS date/time, OLE DATE, NTP time, ULID timestamps, and Chilkat DtObj. It also supports date arithmetic, age/expiration checks, serialization, and local/UTC conversion.
For an extended overview, see CkDateTime Class Overview
Object Creation
DECLARE @hr int
DECLARE @ckDateTime int
EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @ckDateTime OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- ... use @ckDateTime ...
EXEC @hr = sp_OADestroy @ckDateTimeT-SQL uses the Chilkat ActiveX through the OLE Automation stored procedures. They must be enabled once on the server (EXEC sp_configure 'Ole Automation Procedures', 1; RECONFIGURE;), and the Chilkat ActiveX registered must match the bitness of the SQL Server instance (64-bit for a 64-bit SQL Server). To bind to a specific major version of Chilkat, append the major version number to the ProgID, such as sp_OACreate 'Chilkat.CkDateTime.11' for Chilkat v11.*.*.
sp_OACreate returns an object token (an int) that is passed as the first argument of every sp_OAMethod, sp_OAGetProperty and sp_OASetProperty call, and released with sp_OADestroy. Objects returned by methods (such as an HttpResponse or JsonObject) are also tokens received through an int OUT parameter; they must likewise be destroyed, and the OUT parameter is NULL when the method fails to return an object. Objects passed as arguments are passed by their token. When an OLE Automation procedure itself fails (non-zero @hr), sp_OAGetErrorInfo describes the error.
Data types: strings are nvarchar(4000); integers, booleans (1 or 0) and object tokens are int; dates are datetime. In the signatures on this page, @success, @iResult, @sResult and the like are the OUT variables receiving a method's return value, @iValue / @sValue receive or supply a property value, and the remaining @ variables are the method's arguments in order.
A string returned through an OUT parameter is limited to 4000 characters. For longer values, retrieve the result as a result set into a table variable instead of an OUT parameter, for example DECLARE @tmp TABLE (outputLine ntext) followed by INSERT INTO @tmp EXEC sp_OAGetProperty @ckDateTime, 'LastErrorText'. See string length limitations for strings returned by sp_OAMethod calls.
Methods that pass or return raw byte arrays are not shown on this page, because varbinary(max) values cannot be exchanged through sp_OAMethod (see varbinary(max) limitation). Use the BinData-based alternatives (methods ending in Bd) or the base64 / hex string-encoded variants instead. Binary properties (such as LastBinaryResult) can be retrieved as a result set into a table variable, as shown in their signatures. Asynchronous (*Async) methods and event callbacks are not available from SQL Server.
Properties
DebugLogFilePath
EXEC sp_OASetProperty @ckDateTime, 'DebugLogFilePath', @sValue
If set to a file path, this property logs the LastErrorText of each Chilkat method or property call to the specified file. This logging helps identify the context and history of Chilkat calls leading up to any crash or hang, aiding in debugging.
Enabling the VerboseLogging property provides more detailed information. This property is mainly used for debugging rare instances where a Chilkat method call causes a hang or crash, which should generally not happen.
Possible causes of hangs include:
- A timeout property set to 0, indicating an infinite timeout.
- A hang occurring within an event callback in the application code.
- An internal bug in the Chilkat code causing the hang.
IsDst
This is the Daylight Saving Time flag. It can have one of three possible values: 1, 0, or -1. It has the value 1 if Daylight Saving Time is in effect, 0 if Daylight Saving Time is not in effect, and -1 if the information is not available.
Note: This is NOT the DST for the current system time. It is the DST that was in effect at the date value contained in this object.
topLastBinaryResult
This property is mainly used in SQL Server stored procedures to retrieve binary data from the last method call that returned binary data. It is only accessible if Chilkat.Global.KeepBinaryResult is set to 1. This feature allows for the retrieval of large varbinary results in an SQL Server environment, which has restrictions on returning large data via method calls, though temp tables can handle binary properties.
LastErrorHtml
Provides HTML-formatted information about the last called method or property. If a method call fails or behaves unexpectedly, check this property for details. Note that information is available regardless of the method call's success.
topLastErrorText
Provides plain text information about the last called method or property. If a method call fails or behaves unexpectedly, check this property for details. Note that information is available regardless of the method call's success.
LastErrorXml
Provides XML-formatted information about the last called method or property. If a method call fails or behaves unexpectedly, check this property for details. Note that information is available regardless of the method call's success.
topLastMethodSuccess
EXEC sp_OASetProperty @ckDateTime, 'LastMethodSuccess', @iValue
Indicates the success or failure of the most recent method call: 1 means success, 0 means failure. This property remains unchanged by property setters or getters. This method is present to address challenges in checking for null or Nothing returns in certain programming languages. Note: This property does not apply to methods that return integer values or to boolean-returning methods where the boolean does not indicate success or failure.
LastStringResult
In SQL Server stored procedures, this property holds the string return value of the most recent method call that returns a string. It is accessible only when Chilkat.Global.KeepStringResult is set to TRUE. SQL Server has limitations on string lengths returned from methods and properties, but temp tables can be used to access large strings.
LastStringResultLen
The length, in characters, of the string contained in the LastStringResult property.
topUtcOffset
For the current system's timezone, returns the number of seconds offset from UTC for this date/time. The offset includes daylight savings adjustment. Local timezones west of UTC return a negative offset.
topVerboseLogging
EXEC sp_OASetProperty @ckDateTime, 'VerboseLogging', @iValue
If set to 1, then the contents of LastErrorText (or LastErrorXml, or LastErrorHtml) may contain more verbose information. The default value is 0. Verbose logging should only be used for debugging. The potentially large quantity of logged information may adversely affect peformance.
Version
Methods
AddDays
Adds an integer number of days to the date/time. To subtract days, pass a negative integer.
Returns 1 for success, 0 for failure.
topAddSeconds
Adds an integer number of seconds to the date/time. To subtract seconds, pass a negative integer.
Returns 1 for success, 0 for failure.
DeSerialize
Loads the date/time with a string having the format as produced by the Serialize method, which is a string of SPACE separated integers containing (in this order) year, month, day, hour, minutes, seconds, and a UTC flag having the value of 1/0.
topDiffSeconds
Returns the difference in seconds between the dateTimeArg and this date/time. The value returned is this object's date/time - dateTimeArg's date/time. For example, if the returned value is positive, then this object's date/time is more recent than dateTimeArg's date/time. If the return value is negative, then this object's date/time is older than dateTimeArg's date/time.
ExpiresWithin
Returns 1 if the date/time is within n seconds/minutes/hours/days of the current system date/time. Otherwise returns 0. The units can be seconds, minutes, hours, or days (plural or singular).
GetAsDateTime
Returns the date/time in a native format. The .NET implementation returns a .NET System.DateTime structure. The ActiveX implementation returns a Date object.
bLocal indicates whether a local or UTC time is returned.
GetAsDosDate
Returns the date/time as a 32-bit DOS date/time bitmask.
bLocal indicates whether a local or UTC time is returned.
The DOS date/time format is a bitmask:
24 16 8 0 +-+-+-+-+-+-+-+-+ +-+-+-+-+-+-+-+-+ +-+-+-+-+-+-+-+-+ +-+-+-+-+-+-+-+-+ |Y|Y|Y|Y|Y|Y|Y|M| |M|M|M|D|D|D|D|D| |h|h|h|h|h|m|m|m| |m|m|m|s|s|s|s|s| +-+-+-+-+-+-+-+-+ +-+-+-+-+-+-+-+-+ +-+-+-+-+-+-+-+-+ +-+-+-+-+-+-+-+-+ \___________/\________/\_________/ \________/\____________/\_________/ year month day hour minute second
The year is stored as an offset from 1980. Seconds are stored in two-second
increments. (So if the second value is 15, it actually represents 30 seconds.)
GetAsIso8601
Returns the date/time in a compatible ISO 8601 format according to the format specified in formatStr.. Examples of ISO 8601 formats include the following:
YYYY-MM-DD
YYYY-MM-DDThh:mmTZD
YYYY-MM-DDThh:mm:ssTZD
YYYY-MM-DDThh:mm:ss:sssTZD
For the date portion of these formats, YYYY is a four-digit year representation, MM is a two-digit month, and DD is a two-digit day. For the time portion, hh is the hour in 24-hour notation, mm is the two-digit minutes, ss is the two-digit seconds, and sss is the 3-digit milliseconds. A time designator T separates the date and time portions of the string, while a time zone designator TZD specifies a time zone (UTC).
bLocal indicates whether a local or UTC time is returned.
Returns NULL on failure
GetAsOleDate
Returns the date/time in a Windows OLE DATE format.
bLocal indicates whether a local or UTC time is returned.
The OLE automation date format is a floating point value, counting days since midnight 30 December 1899. Hours and minutes are represented as fractional days.
topGetAsRfc822
Returns the date/time as an RFC822 formatted string. (An RFC822 format string is what is found in the Date header field of an email, such as Wed, 18 Oct 2017 09:08:21 GMT.)
bLocal indicates whether a local or UTC time is returned.
Returns NULL on failure
GetAsTimestamp
Returns the date/time as an RFC 3339 formatted string, such as 1990-12-31T23:59:60Z.
(This is an ISO 8061 format like the following: YYYY-MM-DDThh:mm:ssTZD)
bLocal indicates whether a local or UTC time is returned.
Returns NULL on failure
GetAsUnixTime
Returns the date/time as a Unix time value, which is the number of seconds elapsed since 00:00:00 UTC on January 1, 1970.
bLocal indicates whether the date/time returned is local or UTC.
GetAsUnixTimeStr
Returns the time in Unix format (in seconds since the epoch: 00:00:00 UTC on 1 January 1970).
bLocal indicates whether the date/time returned is local or UTC.
Returns NULL on failure
OlderThan
Returns 1 if the date/time is older than the current system date/time by n seconds/minutes/hours/days. Otherwise returns 0. The units can be seconds, minutes, hours, or days (plural or singular).
Serialize
Serializes the date/time to a us-ascii string that can be imported at a later time via the DeSerialize method. The format of the string returned by this method is not intended to match any published standard. It is formatted to a string with SPACE separated integers containing (in this order) year, month, day, hour, minutes, seconds, and a UTC flag having the value of 1 or 0.
Returns NULL on failure
SetFromCurrentSystemTime
Sets the date/time from the current system time.
topSetFromDosDate
Sets the date/time from a 32-bit DOS date/time bitmask. See GetAsDosDate for more information.
topSetFromDtObj
SetFromNtpServer
Sets the date/time by sending a query to an NTP server.
Returns 1 for success, 0 for failure.
SetFromNtpTime
Sets the date/time from a 32-bit NTP time value. ntpSeconds is the number of seconds since 00:00 (midnight) 1 January 1900 GMT.
SetFromOleDate
Sets the date/time from a Windows OLE DATE value.
bLocal indicates whether the passed in date/time is local or UTC.
Note: This method was not working correctly. The problem was discovered just after releasing v9.5.0.65. It will be fixed in the next version update.
topSetFromRfc822
Sets the date/time from an RFC822 date/time formatted string. Here are some examples of RFC822 formatted date/times:
Tue, 15 Nov 2022 22:00:58 +0000 Tue, 15 Nov 2022 20:21:50 +0100 Tue, 01 Nov 2022 18:09:41 -0600
Returns 1 for success, 0 for failure.
topSetFromTimestamp
Sets the date/time from an RFC 3339 timestamp format. (such as 1990-12-31T23:59:60Z:)
(This is an ISO 8061 format like the following: YYYY-MM-DDThh:mm:ssTZD)
Note: Starting in v9.5.0.77, strings formatted as YYMMDDhhmmssZ, such as 181221132225Z, can also be passed to this method.
Returns 1 for success, 0 for failure.
SetFromUlid
Sets this object's date/time using the timestamp embedded in the ulid. If the ulid was not a valid ULID, returns 0 and the date/time is not set. bLocal indicates whether the ulid uses a local time or UTC time.
Returns 1 for success, 0 for failure.
SetFromUnixTime
Sets the date/time from a UNIX time value.
bLocal indicates whether the passed in date/time is local or UTC.
ToDtObj
Copies the date/time to the dtObj. Gets the date/time in the local time if bLocal is 1, or GMT if bLocal is 0.
UlidGenerate
Generates and returns a new ULID using this object's date/time value. bLocal indicates whether to use local time or UTC time.
Returns NULL on failure
UlidIncrement
UlidValidate
Validates the ulid. Returns 1 if the ulid is valid, otherwise returns 0.
Returns 1 for success, 0 for failure.
Deprecated
GetDtObj
Returns that date/time in the date-time object.
This method is deprecated and will be removed in a future version. Applications should use the ToDtObj method.
Returns NULL on failure