Date Related SQL Queries

AML is the language of Aras Innovator, but some external add-ons do connect to Aras Innovator on a direct SQL level. Because Aras Innovator stores DateTimes in SQL Server as UTC, we have provided an add-on function for Microsoft SQL Server that allows the easy conversion of DateTime to and from UTC.

To convert DateTime values from UTC to a specific time zone, use the ConvertToLocal ({value},{Time Zone}) function. The time zone should be specified according to the registry key name of the time zone desired; refer to Aras Innovator - Configuring Internationalization. To use the DEFAULT parameter for the {Time Zone}, it is required that a value be set for CorporateTimeZone.

Example:

select item_number, created_on

from innovator.Document

Would be written as:

select item_number, innovator.ConvertToLocal(created_on,DEFAULT) as CreadtedOn

from innovator.Document

Or:

select item_number, innovator.ConvertToLocal(created_on,'Eastern Standard Time’) as CreadtedOn

from innovator.Document

To convert DateTime values from a specific time zone to UTC, use the ConvertFromLocal({value},{Time Zone}) function. The time zone should be specified according to the registry key name of the time zone desired; refer to Aras Innovator - Configuring Internationalization. To use the DEFAULT parameter for {Time Zone}, it is required that a value be set for CorporateTimeZone.

Example:

update innovator.Document

set effective_date = ‘1/1/2007 00:00:00.00'

Would be written as:

update innovator.Document

set effective_date = innovator.ConvertFromLocal(‘1/1/2007 00:00:00.00',DEFAULT)

Or:

update innovator.Document

set effective_date = innovator.ConvertFromLocal(‘1/1/2007 00:00:00.00','Eastern Standard Time’)

This allows users to write queries in a familiar time zone context.

select item_number, innovator.ConvertToLocal(release_date,DEFAULT) as ReleaseDate

from innovator.Document

where created_on >= innovator.ConvertFromLocal(‘1/1/2007 00:00:00.00',DEFAULT)