How do you access SQL Server timestamp values from C/AL code? When you search for this solution, you typically encounter four common approaches, each with significant drawbacks. Here’s a simpler fifth approach that works seamlessly with NAV 2013 and later versions.

The Traditional Approaches (And Why They Fall Short)

When developers need to access SQL Server timestamp values in NAV, they usually find these solutions:

1. “You Can’t Do It”

The Problem: This isn’t a solution—it’s giving up before trying.

2. ADO.NET with SQL Queries

The Problem: Using ADO.NET to execute SQL queries is overkill for accessing a simple field value.

// Overly complex approach
// Requires SQL connection management
// Breaks NAV's data access patterns

3. SQL Views with Linked Tables

The Problem: This approach creates unnecessary complexity:

  • Requires database schema modifications (views)
  • Complicates upgrade processes
  • Consumes table objects for each timestamp source
  • Doesn’t scale for multiple tables

4. Native Timestamp Fields (NAV 2016+)

The Good: Microsoft introduced proper timestamp support in NAV 2016 (detailed in this blog post).

The Limitations:

  • Requires table customizations (adding BigInteger fields with SQL Timestamp property)
  • Creates upgrade complications
  • Not available for older NAV versions
  • Modifies standard tables

The Elegant Solution: Three Lines of Code

Here’s a clean, simple approach that works without customizations:

IF NOT DataTypeManagement.GetRecordRef(_RelatedRecord, RecRef) THEN
  EXIT(0);
EVALUATE(timestamp, FORMAT(RecRef.FIELD(0).VALUE));

This solution leverages NAV’s built-in capability to access the SQL timestamp through the virtual field 0, which represents the SQL Server rowversion column.

How It Works

Understanding Field 0

Every table in NAV has a virtual field 0 that corresponds to the SQL Server timestamp (rowversion) column. This field:

  • Automatically exists for all tables
  • Requires no customization or table modifications
  • Contains the SQL timestamp as a binary value
  • Updates automatically whenever the record changes

Implementation Details

PROCEDURE GetTimestamp(RelatedRecord : Variant) : Decimal
VAR
  RecRef : RecordRef;
  timestamp : Decimal;
BEGIN
  // Get RecordRef from any record type
  IF NOT DataTypeManagement.GetRecordRef(RelatedRecord, RecRef) THEN
    EXIT(0);
  
  // Access field 0 (SQL timestamp) and convert to decimal
  EVALUATE(timestamp, FORMAT(RecRef.FIELD(0).VALUE));
  EXIT(timestamp);
END;

Usage Examples

For any table record:

// Customer record
Customer.GET('10000');
CustomerTimestamp := GetTimestamp(Customer);

// Item record  
Item.GET('1000');
ItemTimestamp := GetTimestamp(Item);

// Sales Header
SalesHeader.GET(SalesHeader."Document Type"::Order, '1001');
OrderTimestamp := GetTimestamp(SalesHeader);

Practical Applications

1. Optimistic Concurrency Control

// Store original timestamp
OriginalTimestamp := GetTimestamp(Customer);

// Perform operations...
ProcessCustomerData(Customer);

// Verify record hasn't changed
Customer.GET(Customer."No.");
IF GetTimestamp(Customer) <> OriginalTimestamp THEN
  ERROR('Record was modified by another user.');

2. Change Detection

// Before modification
BeforeTimestamp := GetTimestamp(Item);

// Make changes
Item.Description := 'Updated Description';
Item.MODIFY;

// After modification
AfterTimestamp := GetTimestamp(Item);

// Verify change was applied
IF AfterTimestamp = BeforeTimestamp THEN
  ERROR('Record was not updated properly.');

3. Audit Trail Enhancement

// Enhanced audit logging with timestamp
AuditEntry.INIT;
AuditEntry."Table ID" := DATABASE::Customer;
AuditEntry."Record ID" := Customer.RECORDID;
AuditEntry."SQL Timestamp" := GetTimestamp(Customer);
AuditEntry."Modified DateTime" := CURRENTDATETIME;
AuditEntry.INSERT;

Version Compatibility

✅ Supported Versions

  • NAV 2013 and later versions
  • Business Central (all versions)
  • Works with both Classic Client and RTC

❌ Not Supported

  • NAV 2009 R2 and older versions
  • These versions don’t expose field 0 through C/AL

Implementation Notes

Complete Function Example

PROCEDURE GetTimestamp(RelatedRecord : Variant) : Decimal
VAR
  DataTypeManagement : Codeunit "Data Type Management";
  RecRef : RecordRef;
  timestamp : Decimal;
BEGIN
  // Validate input and get RecordRef
  IF NOT DataTypeManagement.GetRecordRef(RelatedRecord, RecRef) THEN
    EXIT(0);
  
  // Access virtual field 0 (SQL timestamp)
  IF NOT EVALUATE(timestamp, FORMAT(RecRef.FIELD(0).VALUE)) THEN
    EXIT(0);
    
  EXIT(timestamp);
END;

Error Handling Considerations

  • Invalid records: Function returns 0 for invalid or empty records
  • Conversion errors: EVALUATE returns FALSE if timestamp conversion fails
  • Null values: New records may have null timestamps until first save

Performance Benefits

This approach offers several advantages:

  • No database overhead: No additional views or tables required
  • No SQL queries: Uses NAV’s native data access
  • Minimal code: Three lines accomplish the task
  • Universal compatibility: Works with any table
  • Upgrade-safe: No customizations to maintain

Common Use Cases

Data Synchronization

Perfect for tracking record changes in integration scenarios:

// Check if record changed since last sync
IF GetTimestamp(Customer) > LastSyncTimestamp THEN
  SynchronizeCustomer(Customer);

Conflict Resolution

Implement optimistic locking without table modifications:

// Store timestamp before long operation
StoredTimestamp := GetTimestamp(SalesHeader);

// After user interaction, verify no changes occurred
IF GetTimestamp(SalesHeader) <> StoredTimestamp THEN
  MESSAGE('Record was modified by another user. Please refresh.');

Download and Try It

Ready to implement this solution? Download the complete implementation:

  • Download FOB (NAV 2013 format)
  • Includes the GetTimestamp function and usage examples
  • Compatible with NAV 2013 through Business Central

Summary

Accessing SQL Server timestamps from C/AL doesn’t require complex solutions. By leveraging NAV’s virtual field 0, you can:

  • Access timestamps from any table without customizations
  • Implement optimistic concurrency control easily
  • Enhance audit trails with precise change tracking
  • Support data synchronization scenarios effectively

This approach provides a clean, maintainable solution that works across NAV versions while avoiding the complexity and limitations of traditional methods.

Have you used SQL timestamps in your NAV projects? Share your experiences in the comments or connect via LinkedIn or Twitter.