Chapter 7: Adding, Changing, and Deleting Data

Entering 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. set language can also affect the format for dates, depending on the default date format for the language you specify. The default language is us_english, and the default date format is mdy. See the set command in the Reference Manual for more information.

dateformat affects only the dates entered as numbers with separators, such as “4/15/90” or “20.05.88”. It does not affect dates where the month is provided in alphabetic format, such as “April 15, 1990” or where there are no separators, such as “19890415”.

Date formats

Adaptive Server recognizes three basic date formats, as described below. Each format must be enclosed in quotes and can be preceded or followed by a time specification, as described under “Entering times”.

The month is entered in alphabetic format.
- Valid formats for specifying the date alphabetically are:
```
  Apr[il] [15][,] 1997
  Apr[il] 15[,] [19]97 
  Apr[il] 1997 [15] 
```
```
  [15] Apr[il][,] 1997 
  15 Apr[il][,] [19]97 
  15 [19]97 apr[il] 
  [15] 1997 apr[il] 
```
```
  1997 APR[IL] [15]
  1997 [15] APR[IL] 
```
- Month can be a three-character abbreviation, or the full month name, as given in the specification for the current language.
- Commas are optional.
- Case is ignored.
- If you specify only the last two digits of the year, values of less than 50 are interpreted as “20yy,” and values of 50 or more are interpreted as “19yy.”
- Type the century only when the day is omitted or when you need a century other than the default.
- If the day is missing, Adaptive Server defaults to the first day of the month.
- When you specify the month in alphabetic format, the dateformat setting is ignored (see the set command in the Reference Manual).
The month is entered in numeric format, in a string with a slash (/), hyphen (-), or period (.) separator.
- The month, day, and year must be specified.
- The strings must be in the form:
```
     <num> <sep> <num> <sep> <num> [ <time spec> ] 
```
  or:
```
     [ <time spec> ]  <num> <sep> <num> <sep> <num> 
```
- The interpretation of the values of the date parts depends on the dateformat setting. If the ordering does not match the setting, either the values are not interpreted as dates, because the values are out of range, or the values are misinterpreted. For example, “12/10/08” can be interpreted as one of six different dates, depending on the dateformat setting. See the set command in the Reference Manual for more information.
- To enter “April 15, 1997” in mdy dateformat, you can use these formats:
```
  [0]4/15/[19]97
  [0]4-15-[19]97
  [0]4.15.[19]97
```
- The other entry orders are shown below with ‘‘/” as a separator; you can also use hyphens or periods:
```
  15/[0]4/[19]97 (dmy) 
  1997/[0]4/15 (ymd) 
  1997/15/[0]4 (ydm) 
  [0]4/[19]97/15 (myd) 
  15/[19]97/[0]4 (dym) 
```
The date is given as an unseparated four-, six-, or eight-digit string, or as an empty string, or only the time value, but no date value, is given.
- The dateformat is always ignored with this entry format.
- If four digits are given, the string is interpreted as the year, and the month is set to January, the day to the first of the month. The century cannot be omitted.
- Six- or eight-digit strings are always interpreted as ymd; the month and day must always be two digits. This format is recognized: [19]960415.
- An empty string (“”) or missing date is interpreted as the base date, January 1, 1900. For example, a time value like “4:33” without a date is interpreted as “January 1, 1900, 4:33AM’’.

The set datefirst command specifies the day of the week (Sunday, Monday, and so on) when weekday or dw is used with datename, and a corresponding number when used with datepart. Changing the language with set language can also affect the format for dates, depending on the default first day of the week value for the language. For the default language of us_english, the default datefirst setting is Sunday=1, Monday=2, and so on; others produce Monday=1, Tuesday=2, and so on. The default behavior can be changed on a per-session basis with set datefirst. See the set command in the Reference Manual for more information.

View this book as PDF