Sending dates and times to the database

The date and time of day can be sent to the database in one of the following ways:

using any interface, as a string
using ODBC or OLE DB, as a binary value (using an ODBC TIMESTAMP_STRUCT structure for example)
using embedded SQL, as a SQLDATETIME structure

The date and time of day with a time zone offset can be sent to the database as a string only.

Date formats

When a date is sent to the database as a string (for the DATE data type) or as part of a string (for the TIMESTAMP or TIMESTAMP WITH TIME ZONE data types), the string can be specified in a number of different ways including that described by ISO 8601, an international standard on the representation of dates and times.

A date can be specified in one of the following ISO 8601 formats.

Calendar date The calendar date format is YYYY-MM-DD where YYYY is the year in the Gregorian calendar, MM is the month of the year between 01 (January) and 12 (December), and DD is the day of the month between 01 and 31. For example, '2010-04-01' represents the first day of April in 2010. ISO 8601 does not require the separator character. Therefore, '20100401' also represents the first day of April in 2010.

ISO calendar date	Format	Example
Basic	YYYYMMDD	20100401
Extended	YYYY-MM-DD	2010-04-01

Week date Another ISO date format is the week date. The format is YYYY-Www-D where YYYY is the year in the Gregorian calendar, W is the letter W, ww is the week of the year between 01 (the first week) and 52 or 53 (the last week), and D is the day in the week between 1 (Monday) and 7 (Sunday). For example, '2010-W13-4' represents the fourth day of the thirteenth week of 2010 (April 1 2010). ISO 8601 does not require the separator character. Therefore, '2010W134' also represents the fourth day of the thirteenth week of 2010. For reduced accuracy, one digit can be omitted from the representation ('2010W13' represents March 29 2010).

ISO week date	Format	Example
Basic	YYYYWwwD	2010W134
Extended	YYYY-Www-D	2010-W13-4

Ordinal date The last ISO date format is the ordinal date. The format is YYYY-DDD where YYYY is the year in the Gregorian calendar and DDD is the ordinal number of a calendar day within the calendar year. For example, '2010-091' represents the first day of April in 2010. ISO 8601 does not require the separator character. For example, '2010091' also represents April 1 2010. The maximum ordinal date is 366 for those years with leap years. For example, '2008366' represents the last day of the year in 2008 (December 31 2008).

ISO ordinal date	Format	Example
Basic	YYYYDDD	2010091
Extended	YYYY-DDD	2010-091

Other date formats are supported. SQL Anywhere is very flexible in its interpretation of strings containing dates. Whenever there is any ambiguity, the interpretation of the date value is guided by the date_order and nearest_century database option settings. For example, depending on the date_order setting, '02/05/2002' can be interpreted by the database server as the 2nd of May (DMY), or the 5th of February (MDY), or an illegal value (YMD).

The nearest_century setting determines whether a two-digit year value is interpreted as a year in the twentieth or twenty-first century. For example, in the string '02/05/10', the date_order setting would determine whether 02 or 10 is interpreted as the year and the nearest_century setting would determine whether 02 represented 1902 or 2002, or whether 10 represented 1910 or 2010. The value of the nearest_century option affects the interpretation of 2-digit years: 2000 is added to values less than nearest_century and 1900 is added to all other values. The default value of this option is 50. So, by default, the year 50 is interpreted as 1950 and the year 49 is interpreted as 2049.

The following table shows how the first day of April in 2010 could be specified using the indicated date_order setting and a nearest_century setting of 50.

date_order	Format	Example
YMD	YYYY/MM/DD	2010/04/01
YMD	YY/MM/DD	10/04/01
MDY	MM/DD/YYYY	04/01/2010
MDY	MM/DD/YY	04/01/10
DMY	DD/MM/YYYY	01/04/2010
DMY	DD/MM/YY	01/04/10

Since ISO 8601 formats are not ambiguous and are not affected by the user's setting of date_order and nearest_century, their use is recommended.

Dates can also be specified using month names. Examples are '2010 April 01', 'April 1, 2010', and '1 April 2010'. When the year is ambiguously specified, the date_order option is used to factor the year and day of month parts. Therefore, '01 April 10' is interpreted as April 10 2001 when the date_order is 'YMD' or as April 1 2010 when the date_order is 'DMY'.

The year in a date can range from 0001 to 9999. The minimum date in SQL Anywhere is 0001-01-01.

If a string contains only a partial date specification, default values are used to fill out the date. The following defaults are used:

year The current year is used when no year is specified (for example, 'April 1').
month The current month is used when no year and month are specified (for example, '23:59:59') or 01 if a year is specified (for example, '2010').
day The current day is used when no year and month are specified (for example, '23:59:59') or 01 if a month is specified (for example, 'April').

In the following example, the date value is constructed from the current date.

SELECT CAST('23:59:59' AS TIMESTAMP)

Time formats

The time of day can be specified in the ISO 8601 format, using the 24-hour timekeeping system. It is hh:mm:ss, where hh is the number of complete hours that have passed since midnight, mm is the number of complete minutes since the start of the hour, and ss is the number of complete seconds since the start of the minute. For example, '23:59:59' represents the time one second before midnight.

The ISO 8601 standard allows for the omission of seconds and minutes. For example, '23:59' represents the time sixty seconds before midnight.

The ISO 8601 standard also allows you to include a decimal fraction to the seconds unit. Fractional seconds are specified using a comma (,) or a period (.). The fraction is stored to a maximum of six decimal places. For example, '23:59:59,500000' and '23:59:59.500000' both represent the time one-half second before midnight. SQL Anywhere does not support fractional minutes or hours.

ISO 8601 does not require the colon separator character when the time of day is included with a date specification. For example, '235959' represents the time one second before midnight.

The maximum time of day is '24:00:00'. It represents midnight. When combined with a date, it represents midnight, or 00:00:00 of the next day. For example, '2010-04-01 24:00:00' is equivalent to '2010-04-02 00:00:00'.

ISO time	Format	Example
Basic (with date)	hhmmss.ssssss	20100401 235959.500000
Basic (with date)	hhmmss,ssssss	20100401 235959,500000
Extended	hh:mm:ss.ssssss	23:59:59.500000
Extended	hh:mm:ss,ssssss	23:59:59,500000

The non-ISO AM and PM designators are also supported. For example, '11:59:59 PM' is equivalent to '23:59:59'.

AM/PM	Format	Example
AM	hh:mm:ss.ssssss AM	11:59:59.500000 AM
AM	hh:mm:ss,ssssss AM	11:59:59,500000 AM
PM	hh:mm:ss.ssssss PM	11:59:59.500000 PM
PM	hh:mm:ss,ssssss PM	11:59:59,500000 PM

Date with time formats

ISO 8601 permits the date and time of day to be combined using a space character or the letter T. For example, '2010-04-01 23:59:59' and '2010-04-01T23:59:59' both represent the time one second before midnight on the first day of April in 2010. The hyphen and colon separator characters can be omitted. For example, '20100401T235959' also represents the same date and time.

SQL Anywhere supports the mixing of basic and extended date and time formats. For example, '20100401T23:59:59' combines both the basic and extended formats.

Time zone formats

ISO 8601 also permits the addition of a time zone offset to a date and time of day string. The format is one of:

Z (Zulu) The date and time of day are in Coordinated Universal Time (UTC). For example, '2010-04-01 23:00:00Z' represents 11:00 PM Coordinated Universal Time on the first day of April in 2010.
+hh:mm The specified date and time of day are the indicated number of hours and minutes ahead of UTC. For example, '2010-04-01 23:00:00+04:00' represents 11:00 PM on the first day of April in 2010 in a time zone 4 hours east of UTC.
-hh:mm The specified date and time of day are the indicated number of hours and minutes behind UTC. For example, '2010-04-01 23:00:00-05:00' represents 11:00 PM on the first day of April in 2010 in a time zone 5 hours west of UTC.

If the minutes are 0, they do not need to be specified in the time zone offset. Also, a space can precede the time zone offset. For example, '2010-04-01 23:00:00 -03:30' represents 11:00 PM on the first day of April in 2010 in a time zone three and a half hours west of UTC.

ISO time zone	Format	Example
Basic	Z	20100401 235959Z
Basic	+hhmm	20100401 235959+0400
Basic	+hh	20100401 235959+04
Basic	-hhmm	20100401 235959-0500
Basic	-hh	20100401 235959-05
Basic	using T, fraction	20100401T235959.50-0330
Extended	Z	2010-04-01 23:59:59Z
Extended	+hh:mm	2010-04-01 23:59:59+04:00
Extended	-hh:mm	2010-04-01 23:59:59-05:00
Extended	using T, fraction	2010-04-01T23:59:59.50-03:30

SQL Anywhere supports the mixing of basic and extended date, time, and time zone formats. For example, '20100401T23:59:59-05' combines both basic and extended formats.