Entry Format for Date/Time Values

Enter datetime and smalldatetime values as character strings, enclosed in single quotation marks.

Replication Server encloses datetime values in single quotation marks when it substitutes datetime values into function-string output templates. Be sure to consider this when you create function strings that include datetime variables.

The date and time portions of the data are recognized separately; therefore, the time can precede or follow the date. If you omit the time, Replication Server assumes midnight (12:00:00:000AM). If you omit the date, Replication Server assumes January 1, 1900.

Enter times according to these general rules:

Hours range from 0 to 23; minutes and seconds range from 0 to 59; milliseconds range from 0 to 999.
A value must have a colon or an “AM” or “PM” indicator to be recognized as a time value.
You can append “AM” or “PM,” with or without an intervening space. 12AM is midnight and 12PM is noon. If you specify AM, the hour must be between 1 and 12 (0 is acceptable in place of 12). If you specify PM, the hour must be between 13 and 23.
Milliseconds can be preceded by either a colon or a period. If preceded by a colon, the number means thousandths of a second. If preceded by a period, a single digit means tenths of a second, two digits mean hundredths of a second, and three digits mean thousandths of a second. For example, “12:30:20:1” means twenty and one-thousandth of a second past 12:30; “12:30:20.1” means twenty and one-tenth of a second past 12:30.
You can omit any portion of a time value. If you omit seconds, you must also omit milliseconds. If you omit minutes, you must also omit seconds and milliseconds. Replication Server assumes zero for any omitted part.

Here are some examples of time literals:

2:00
14.30
14:30:20
14:30:20:500
4pm
11:41:36 AM
12:48:5.333 pm

Enter dates with the month, day, and year in any order, subject to the following rules:

You can enter the month as a number from 1–12, or use the U.S. English month name or its three-character abbreviation.
If you use the numeric month, the date parts must be separated with slashes (/), hyphens (-), or periods (.). The date parts must be given in month-day-year order.
These examples show different ways to enter the date March 15, 1998:
```
3-15-1998
March-15-1998
March 15 1998
15/March/1998
March.15.1998
```
You can abbreviate U.S. English months to 3 characters. Case is not significant.
```
JAN 9 1998
31 oct 1997
```
When you use an alphabetic month, the month and day can be followed by a comma. These are valid dates:
```
Nov 17, 1997
1997 Nov, 17,
17 Nov, 1997
```
You can enter the year with one, two, or four digits. A one- or two-digit year less than 50 is assumed to be in the current (twenty-first) century. A two-digit year greater than or equal to 50 is in the last (twentieth) century.
Four-digit years are recognized anywhere in a date value. Two-digit years must appear after the day of the month.
You can omit the day of the month if you use the alphabetic month and a four-digit year. The day defaults to the first of the month. You cannot use separators other than commas after the month name.
Replication Server interprets these dates as May 1, 1998:
```
May 1998
1998 MAY
may, 1998
```

These examples show how to use bigdatetime and bigtime in a replication definition, a function replication definition, and a subscription. In these examples:

PDS – primary data server
pdb1 – primary database
RDS – replicate data server
rdb1 – replicate database
tb1 – table
col1, col2, col3 – columns
rep1 – replication definition
func1 – function replication definition
sub1 – subscription

Example 1

Using the datatypes in a replication definition.

create replication definition rep1
with primary at PDS.pdb1
with all tables named tb1
(col1 int, col2 bigdatetime, col3 bigtime)
primary key (col1)

Example 2

Using the datatypes in a function replication definition.

create function replication definition func1
with primary at PDS.pdb1
(@par1 int, @par2 bigdatetime, @par3 bigtime)
searchable parameters (@par1)

Example 3

Using the datatypes in a subscription.

create subscription sub1 for rep1
with replicate at RDS.rdb1
where col3 = ‘14:20:00.010101’
without materialization