Date and Time

The SAP ASE datatypes used to store date and time information include datetime, smalldatetime, date, time, bigdatetime, and bigtime.

All date and time data must be enclosed in single or double quotes when you enter it as a literal.

Display and entry formats for date and time data provide a wide range of date output formats, and recognize a variety of input formats. The display and entry formats are controlled separately. The default display format provides output that looks like “Apr 15 2003 10:23PM.” The convert command provides options to display seconds and milliseconds and to display the date with other date-part ordering.

SAP ASE recognizes a wide variety of data entry formats for dates. Case is always ignored, and spaces can occur anywhere between date parts. When you enter datetime and smalldatetime values, always enclose them in single or double quotes. Use single quotes if the quoted_identifier option is on; if you use double quotes, SAP ASE treats the entry as an identifier.

SAP ASE recognizes the two date and time portions of the data separately, so the time can precede or follow the date. Either portion can be omitted, in which SAP ASE uses the default. The default date and time is January 1, 1900, 12:00:00:000AM.

For datetime, the earliest date you can use is January 1, 1753; the latest is December 31, 9999. For smalldatetime, the earliest date you can use is January 1, 1900; the latest is June 6, 2079. For bigdatetime, the earliest date you can enter is January 1, 0001 and the latest is December 31, 9999. For date, the earliest date you can use is January 1, 0001; the latest is December 31, 9999. Dates earlier or later than these dates must be entered, stored, and manipulated as char, or unichar; or varchar or univarchar values. SAP ASE rejects all values it cannot recognize as dates between those ranges.

For time, the earliest time is 12:00AM; the latest is 11:59:59:999. For bigtime, the earliest time is 12:00:00.000000AM; the latest is 11:59:59.999999PM.

• Enter Times
  The order of time components is significant. Enter the hours first; then minutes; then seconds; then milliseconds; then AM (or am) or PM (pm).
• Enter Dates
  The set dateformat command specifies the order of the date parts (month, day, and year) when dates are entered as strings of numbers with separators.
• Search Dates and Times
  You can use the like keyword and wildcard characters with datetime, smalldatetime, bigdatetime, bigtime, date, and time data, as well as with char, unichar, nchar, varchar, univarchar, nvarchar, text, and unitext.