Datetime Data Type

ANSI and ISO 8601 Compliance

Datetime is not ANSI or ISO 8601 compliant.

Numeric date formats:

Datetime string literals are in single quotation marks ('), for example, 'string_literaL'.

If the environment is not us_english, the string literals should be in the format N'string_literaL'.

You can specify date data with a numeric month specified.

For example, 5/20/97 represents the twentieth day of May 1997.

When you use numeric date format, specify the month, day, and year in a string that uses slash marks (/), hyphens (-), or periods (.) as separators.

This string must appear in the following form:

·         number separator number separator number [time] [time]

When the language is set to us_english, the default order for the date is mdy.

You can change the date order by using the SET DATEFORMAT statement.

The setting for SET DATEFORMAT determines how date values are interpreted. If the order does not match the setting, the values are not interpreted as dates, because they are out of range or the values are misinterpreted.

For example, 12/10/08 can be interpreted as one of six dates, depending on the
DATEFORMAT setting. A four-part year is interpreted as the year.

The SET DATEFORMAT session setting is applied when you specify numeric month.
Note:

• We should supply input date values as in order of SET DATEFORMAT

• If we specify year as first part then month and day parts will be interpreted as in order of SET DATEFORMAT.

Alphabetical date formats:

You can specify date data with a month specified as the full month name. For example, April or the month abbreviation of Apr specified in the current language; commas are optional and capitalization is ignored.

Here are some guidelines for using alphabetical date formats:

·         Enclose the date and time data in single quotation marks ('). For languages other than English, use N'

·         If you specify only the last two digits of the year, values less than the last two digits of the value of the Configure the two digit year cutoff Server Configuration Option configuration option are in the same century as the cutoff year. Values greater than or equal to the value of this option are in the century that comes before the cutoff year. For example, if two digit year cutoff is 2050 (default), 25 is interpreted as 2025 and 50 is interpreted as 1950. To avoid ambiguity, use four-digit years.

·         If the day is missing, the first day of the month is supplied.

The SET DATEFORMAT session setting is not applied when you specify the month in alphabetical form.

Valid date formats:

• All are valid date formats with month in alphabetical form.

select orderdate from orders where orderdate='1997 dec 31'

select orderdate from orders where orderdate='1997 31 dec'

select orderdate from orders where orderdate='31 1997 dec'

select orderdate from orders where orderdate='31 dec 1997'

select orderdate from orders where orderdate='dec 31 1997'

select orderdate from orders where orderdate='dec 1997 31'

Note:

• The space between date and year or year and date part is mandatory in the below few formats otherwise sql server will throw an error because it fails to interpret day /year parts.

'31 1997 dec'

'dec 31 1997'

'dec 1997 31'

ISO 8601:

To use the ISO 8601 format, you must specify each element in the format. This also includes the T, the colons (:), and the period (.) that are shown in the format.

The brackets indicate that the fraction of second component is optional. The time component is specified in the 24-hour format.
The T indicates the start of the time part of the datetime value.

The advantage in using the ISO 8601 format is that it is an international standard with unambiguous specification. Also, this format is not affected by the SET DATEFORMAT or SET LANGUAGE setting.

UNAMBIGUOS DATE FORMATS: (Work regardless of server date and language setting)

YYYY-MM-DDThh:mm:ss[.mmm]   1997-12-31T00:00:00

YYYYMMDD[ hh:mm:ss[.mmm]]   19971231 (standard format)

Rounding of datetime Fractional Second Precision

Datetime values are rounded to increments of .000, .003, or .007 seconds, as shown in the following table.

User-specified value	System stored value
01/01/98 23:59:59.999	1998-01-02 00:00:00.000
01/01/98 23:59:59.995 01/01/98 23:59:59.996 01/01/98 23:59:59.997 01/01/98 23:59:59.998	1998-01-01 23:59:59.997
01/01/98 23:59:59.992 01/01/98 23:59:59.993 01/01/98 23:59:59.994	1998-01-01 23:59:59.993
01/01/98 23:59:59.990 01/01/98 23:59:59.991	1998-01-01 23:59:59.990

Last Digit	Rounded value
0 or 1	0
2,3 or 4	3
5,6,7 or 8	7
9	0 ( increase previous digit)

Internal Storage:

SQL Server stores datetime values as a two 4-byte integers

First 4-byte for Date value (number of days from base date 1900-01-01)

Second 4-bytes for time value (number of milliseconds after midnight)

Convertion of Datetime into Integers:

declare @mydate datetime

set @mydate='1900-01-20 00:00:10:456'

select

@mydate as source_date,

datediff(day, '1900-01-01',@mydate) as no_of_days,

convert(char(15),@mydate,114) as time_part, 

datediff(millisecond, '1900-01-01',convert(char(15),@mydate,114)) as number_of_milliseconds

source_date                       no_of_days  time_part       number_of_milliseconds

------------------------------------------------------ ----------- --------------- --------

1900-01-20 00:00:10.000           19          00:00:10:000    10000

(1 row(s) affected)

The datetime value converted into integer values these values stored in the column.

Conversion form integer value to datetime value:

select dateadd(ms,10457,dateadd(dd,19,'1900-01-01')) as source_date

source_date                                                      

------------------------------------------------------

1900-01-20 00:00:10.457

(1 row(s) affected)

Run the following statement to know the server set options for the current connection

DBCC useroptions

Millisecond expression
There is a difference in expressing the millisecond if it is less than 3 digits

Let us take this example

select 

        cast('2010-01-01 12:45:34.79' as datetime) as date1, 

        cast('2010-01-01 12:45:34:79' as datetime) as date2

The result is

date1                   date2

----------------------- -----------------------

2010-01-01 12:45:34.790 2010-01-01 12:45:34.080

As you see there is a different millisecond value in the result because

when a dot is used,
1 A single digit denotes tenths-of-a-second
2 Two digits denote hundredths-of-a-second
3 Three digits denote thousandsths-of-a-second

when a colon is used, the number denotes thousandsths-of-a-second

So, in short, if the number of milliseconds is less than 3, Zeroes are added on the right side if a dot is used and Zeroes are added on the left side if a colon is used in order to make it three digits and rounded if necessary.

data type	Format	Range	Accuracy	Storage size (bytes)	User-defined fractional second precision	Time zone offset
datetime	YYYY-MM-DD hh:mm:ss[.nnn]	1753-01-01 through 9999-12-31	0.00333  second	8	No	No

Thanks..