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 function ConvertToLocal ({value},{Time Zone}). The time zone should be specified according to the registry key name of the time zone desired. (Refer to section Corporate Time Zone.) To use the DEFAULT parameter for 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 function ConvertFromLocal({value},{Time Zone}). The time zone should be specified according to the registry key name of the time zone desired. (Refer to section Corporate Time Zone) 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.Documnet

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)