Get best of the week

SQL Server Date and Time Errors

The translation of the article was prepared especially for students of the course "MS SQL Server Developer" .




Content



#1: ,


, , - , SQL Server . ( SQL Server , .)


T-SQL- , / , "05-07-2015 10:05:23.187". . , SQL Server / ( ). , , ( — , ).


DATETIME. SQL Server, DATETIME . , — . 1 1753 31 9999 . — 00:00:00.000 23:59:59.997. 1900-01-01 00:00:00.000.


. 1 1900 .
, — . , 1 1899 — -365, 1 1901 — 365. , SQL Server 0,003 . , 00:00:00.003 1, 00:00:01.000 300.


, DATETIME . , DATETIME:


DECLARE @a DATETIME = '2015-05-07 10:05:23.187'
SELECT @a;

, SELECT :


2015-05-07 10:05:23.187

, , VARBINARY:


DECLARE @a DATETIME = '2015-05-07 10:05:23.187'
SELECT CONVERT(VARBINARY(8), @a);

, -:


0x0000A49100A6463C

SQL Server DATETIME (int), 8 ( 4 ). 4 (0000A491) , 4 (00A6463C) . , SUBSTRING, :


DECLARE @a DATETIME = '2015-05-07 10:05:23.187'
SELECT SUBSTRING(CONVERT(VARBINARY(8), @a), 1, 4);

SELECT , :


0x0000A491

VARBINARY INT. DATETIME:


DECLARE @a DATETIME = '2015-05-07 10:05:23.187'
SELECT 
   SUBSTRING(CONVERT(VARBINARY(8), @a), 1, 4) AS DateBinary,
   CAST(SUBSTRING(CONVERT(VARBINARY(8), @a), 1, 4) AS INT) AS DateInt,
   SUBSTRING(CONVERT(VARBINARY(8), @a), 5, 4) AS TimeBinary,
   CAST(SUBSTRING(CONVERT(VARBINARY(8), @a), 5, 4) AS INT) AS TimeInt;

SELECT:


DateBinaryDateIntTimeBinaryTimeInt
0x0000A491421290x00A6463C10896956

, 1 1900 42 129 , 10 .


188 :


DECLARE @a DATETIME = '1827-12-03 22:15:11.297'
SELECT 
   SUBSTRING(CONVERT(VARBINARY(8), @a), 1, 4) AS DateBinary,
   CAST(SUBSTRING(CONVERT(VARBINARY(8), @a), 1, 4) AS INT) AS DateInt,
   SUBSTRING(CONVERT(VARBINARY(8), @a), 5, 4) AS TimeBinary,
   CAST(SUBSTRING(CONVERT(VARBINARY(8), @a), 5, 4) AS INT) AS TimeInt;

1 1900 26 327 , — 24 :


DateBinaryDateIntTimeBinaryTimeInt
0xFFFF9929-263270x016EB86D24033389

( 0):


DECLARE @a DATETIME = '1900-01-01 00:00:00.000'
SELECT 
   SUBSTRING(CONVERT(VARBINARY(8), @a), 1, 4) AS DateBinary,
   CAST(SUBSTRING(CONVERT(VARBINARY(8), @a), 1, 4) AS INT) AS DateInt,
   SUBSTRING(CONVERT(VARBINARY(8), @a), 5, 4) AS TimeBinary,
   CAST(SUBSTRING(CONVERT(VARBINARY(8), @a), 5, 4) AS INT) AS TimeInt;

, VARBINARY INT :


DateBinaryDateIntTimeBinaryTimeInt
0x0000000000x000000000

, , , DATETIME. :


DECLARE @a DATETIME = '1900-01-02 00:00:01.000'
SELECT 
   SUBSTRING(CONVERT(VARBINARY(8), @a), 1, 4) AS DateBinary,
   CAST(SUBSTRING(CONVERT(VARBINARY(8), @a), 1, 4) AS INT) AS DateInt,
   SUBSTRING(CONVERT(VARBINARY(8), @a), 5, 4) AS TimeBinary,
   CAST(SUBSTRING(CONVERT(VARBINARY(8), @a), 5, 4) AS INT) AS TimeInt;

. — 1, — 300:


DateBinaryDateIntTimeBinaryTimeInt
0x0000000110x0000012C300

, DATETIME. , / SQL Server . DATETIME2, (7):


DECLARE @b DATETIME2 = '2015-05-07 10:05:23.1872436'
SELECT CONVERT(VARBINARY(10), @b);

, DATETIME:


0x07B4854E9254EC390B

DATETIME2 SQL Server (07), (EC390B), (B4854E9254), . DATE TIME . , DATE:


DECLARE @b DATE = '2015-05-07 10:05:23.1872436'
SELECT CONVERT(VARBINARY(10), @b);

, :


0xEC390B

TIME:


DECLARE @b TIME = '2015-05-07 10:05:23.1872436'
SELECT CONVERT(VARBINARY(10), @b);

, DATETIME2:


0x07B4854E9254

:


DECLARE @b DATETIME2(4) = '2015-05-07 00:00:00.0001'
SELECT CONVERT(VARBINARY(10), @b);

SELECT :


0x0401000000EC390B

, (04), , (01000000). , , SQL Server DATETIME2, DATE TIME , DATETIME, , , , , , , .


, SQL Server, , , . , /.


#2: ,


T-SQL , , , , SQL Server. , () . /. SQL Server , , , 15481099 24033389.


SQL Server , /. . british ( ) VARCHAR DATETIME:


SET LANGUAGE british;
DECLARE @a VARCHAR(20) = '19-05-2015 10:11:12';
SELECT CAST(@a AS DATETIME);

, SELECT :


2015-05-19 10:11:12.000

US English ( ) :


SET LANGUAGE us_english;
DECLARE @a VARCHAR(20) = '19-05-2015 10:11:12';
SELECT CAST(@a AS DATETIME);

SQL Server :


Msg 242, Level 16, State 3, Line 730
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Msg 242, Level 16, State 3, Line 730
   varchar    datetime       .

: --. , SQL Server british, US English. SQL Server, US English, , , 19 , . , , :


SET LANGUAGE us_english;
DECLARE @a VARCHAR(20) = '2015-05-19 10:11:12';
SELECT CAST(@a AS DATETIME);

SELECT . british , "out-of-range".


, , , . , / , , "--". , , / :


SET LANGUAGE british;
DECLARE @a VARCHAR(20) = '2015-05-19 10:11:12';
SELECT CAST(@a AS DATETIME);

. , SQL Server . , , , "--". , , SELECT :


Msg 242, Level 16, State 3, Line 204
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Msg 242, Level 16, State 3, Line 204
   varchar    datetime       .

, , , DATETIME2:


SET LANGUAGE british;
DECLARE @a VARCHAR(20) = '2015-05-19 10:11:12';
SELECT CAST(@a AS DATETIME2);

SELECT :


2015-05-19 10:11:12.0000000

, "--" SQL Server, DATETIME, DATETIME2.


DATETIME2, "--", , SQL Server 2008 . . , . , ISO 8601:


SET LANGUAGE british;
DECLARE @a VARCHAR(20) = '2015-05-19T10:11:12';
SELECT CAST(@a AS DATETIME);

. /, , , — , .


#3: ,


— , , , . , .


, / SQL Server . , , , , 15 2015 3:30 . ? ? SQL Server, , ? UTC? - , , .


, , . , , , , . ? , , , .


, , , . ( ).


, SQL Server 2008 DATETIMEOFFSET, / . DATETIME2 , ( UTC).


:


DECLARE @pdt DATETIMEOFFSET = SYSDATETIMEOFFSET();
SELECT @pdt;

SYSDATETIMEOFFSET DATETIMEOFFSET, , , UTC-:


2015-05-08 17:57:23.5350000 -07:00

/ UTC , . , DATENAME:


DECLARE @pdt DATETIMEOFFSET = SYSDATETIMEOFFSET();
SELECT DATENAME(tzoffset, @pdt);

, SELECT UTC:


-07:00

, DATETIMEOFFSET UTC-:


DECLARE 
  @utc DATETIMEOFFSET = SYSUTCDATETIME(),
  @pdt DATETIMEOFFSET = SYSDATETIMEOFFSET();
SELECT @utc AS UTC, @pdt AS PDT;

, UTC (PDT) .


UTCPDT
2015-05-09 00:57:37.1820000 +00:002015-05-08 17:57:37.1820000 -07:00

SQL Server 2008 SWITCHOFFSET DATETIMEOFFSET :


DECLARE @a DATETIMEOFFSET = '2015-05-08 17:57:53.3390000 -07:00';
SELECT SWITCHOFFSET(@a, '-05:00');

UTC- -07:00 -05:00 :


2015-05-08 19:57:53.3390000 -05:00

, SQL Server , , SQL Server 2008 . , SQL Server — .


, , 1 2015 :


DECLARE 
  @mel DATETIMEOFFSET = '2015-04-01 17:57:53.3390000 +11:00',
  @sea DATETIMEOFFSET = '2015-04-01 17:57:53.3390000 -07:00';
SELECT DATEDIFF(hh, @mel, @sea);

:


18

, , 18- . 1 2015 , UTC:


DECLARE 
  @mel DATETIMEOFFSET = '2015-05-01 17:57:53.3390000 +11:00',
  @sea DATETIMEOFFSET = '2015-05-01 17:57:53.3390000 -07:00';
SELECT DATEDIFF(hh, @mel, @sea);

:


18

18 , 17, 5 . , 5- , +10:00.


, , , . . , , . , . - , , / .


, . , . . , , .


, DATETIMEOFFSET /. / . , , .


#4: DATETIME2 DATETIME


, DATETIME2 SQL Server 2008, DATETIME , - . , , DATETIME2 DATETIME.


, :


DECLARE 
  @a DATETIME2 = '2015-05-12 09:47:12.5556789',
  @b DATETIME  = '2015-05-12 09:47:12.555';
SELECT @a AS DateTime2Type, @b AS DateTimeType;

DATETIME2 , DATETIME . .


DateTime2TypeDateTimeType
2015-05-12 09:47:12.45567892015-05-12 09:47:12.457

, , SQL Server' DATETIME .003 , .. .000, .003 0,007 . (. — 1/300 = 0,0033333333333333… , - .000, .003, .007, .010, .013, ...)


DATETIME2 . , , . , .555678999 .5556790, , .9999999, .


DATETIME2 , DATETIME. , DATETIME, DATETIME2. , DATETIME2 3:


DECLARE 
  @a DATETIME2(3) = '2015-05-12 09:47:12.5556789',
  @b DATETIME     = '2015-05-12 09:47:12.555';
SELECT @a AS DateTime2Type, @b AS DateTimeType;

, DATETIME2 , DATETIME.


DateTime2TypeDateTimeType
2015-05-12 09:47:12.5562015-05-12 09:47:12.557

, DATETIME2 , , , , DATETIME. , SQL Server 7 DATETIME2 8 DATETIME.


DATETIME2 8 4, 6 , 3. C DATETIME2 , , . DATETIME2 :


DECLARE 
  @a DATETIME2(0) = '2015-05-12 09:47:12.5556789',
  @b DATETIME     = '2015-05-12 09:47:12.555';
SELECT @a AS DateTime2Type, @b AS DateTimeType;

— , . :


DateTime2TypeDateTimeType
2015-05-12 09:47:132015-05-12 09:47:12.557

DATETIME2 DATETIME , DATETIME2 ( 1 0001). DATETIME 1 1753 . ( 9999 , , , ).


DATE TIME, .


DECLARE 
  @a DATE = '2015-05-12 09:47:12.5556789',
  @b TIME = '2015-05-12 09:47:12.5556789';
SELECT @a AS DateType, @b AS TimeType;

, DATE TIME /:


DateTypeTimeType
2015-05-1209:47:12.5556789

, , ( SQL Server 2008 , DATETIME2).


DATETIME2 , , / .NET. , DATETIME2 /, . , DATETIME.


#5: /


, . , DATETIME SMALLDATETIME. , , DATETIME2:


DECLARE 
  @a DATETIME2 = '2015-05-12 23:32:12.1234567',
  @b DATETIME2 = '2015-05-12 23:32:12.123456789',
  @c DATETIME2 = '2015-05-12 23:59:59.999999999';
SELECT '2015-05-12 23:32:12.1234567' AS OrigValue, @a AS StoredValue
UNION ALL
SELECT '2015-05-12 23:32:12.123456789', @b
UNION ALL
SELECT '2015-05-12 23:59:59.999999999', @c;

DATETIME2 7 — . , @a , @b @c :


OrigValueStoredValue
2015-05-12 23:32:12.12345672015-05-12 23:32:12.1234567
2015-05-12 23:32:12.1234567892015-05-12 23:32:12.1234568
2015-05-12 23:59:59.9999999992015-05-13 00:00:00.0000000

@b , : , 123456789 1234568. @c . , , . SQL Server . , , .


, DATETIME:


DECLARE 
  @a DATETIME = '2015-05-12 23:59:59.996',
  @b DATETIME = '2015-05-12 23:59:59.998',
  @c DATETIME = '2015-05-12 23:59:59.999';
SELECT '2015-05-12 23:59:59.996' AS OrigValue, @a AS DatetimeValue
UNION ALL
SELECT '2015-05-12 23:59:59.998', @b
UNION ALL
SELECT '2015-05-12 23:59:59.999', @c;

@a , @b , @c :


OrigValueStoredValue
2015-05-12 23:59:59.9962015-05-12 23:59:59.997
2015-05-12 23:59:59.9982015-05-12 23:59:59.997
2015-05-12 23:59:59.9992015-05-13 00:00:00.000

, , , , DATETIME, . , SQL Server DATETIME .000, .003 .007 . , . , , , .


, . , , DATETIME2 DATETIME:


DECLARE @a DATETIME2 = '2015-05-12 23:59:59.9986789';
DECLARE @b DATETIME  = @a;
SELECT @a AS Datetime2Value, @b AS DatetimeValue;

, DATETIME , :


Datetime2ValueDatetimeValue
2015-05-12 23:59:59.99867892015-05-13 00:00:00.000

SMALLDATETIME:


DECLARE 
  @a SMALLDATETIME = '2015-05-12 23:22:22',
  @b SMALLDATETIME = '2015-05-12 23:22:30',
  @c SMALLDATETIME = '2015-05-12 23:22:52',
  @d SMALLDATETIME = '2015-05-12 23:59:52';
SELECT '2015-05-12 23:22:22' AS OrigValue, @a AS SmalldatetimeValue
UNION ALL
SELECT '2015-05-12 23:22:30', @b
UNION ALL
SELECT '2015-05-12 23:22:52', @c
UNION ALL
SELECT '2015-05-12 23:59:52', @d;

, "00" , SMALLDATETIME ,


OrigValueSmalldatetimeValue
2015-05-12 23:22:222015-05-12 23:22:00
2015-05-12 23:22:302015-05-12 23:23:00
2015-05-12 23:22:522015-05-12 23:23:00
2015-05-12 23:59:522015-05-13 00:00:00

@a : 22 , . @b , SQL Server 30 . @c. , @d , 59 , , 23 .


, , :


DECLARE @a SMALLDATETIME = '2015-05-12 23:59:29.999';
SELECT @a;

:


2015-05-13 00:00:00

, DATETIME SMALLDATIME, , . DATETIME2, .


#6:


. SQL Server 2008 , , DATE, :


DECLARE @a DATETIME2(3) = '2015-05-12 22:14:18.003';
SELECT CAST(@a AS DATE);

DATETIME2 DATE, :


2015-05-12

DATETIME DATE :


DECLARE @a DATETIME = '2015-05-12 22:14:18.003';
SELECT CAST(@a AS DATE);

TIME:


DECLARE @a DATETIME = '2015-05-12 22:14:18.003';
SELECT CAST(@a AS TIME(3));

, SELECT :


22:14:18.003

, DATE TIME. , . .


, . :


DECLARE @a DATETIME = '2015-05-12 22:14:18.003';
SELECT CAST(CONVERT(CHAR(8), @a, 112) AS DATETIME);

, , ISO (112), DATETIME, :


2015-05-12 00:00:00.000

, SQL Server . , , .


DATEADD DATEDIFF :


DECLARE @a DATETIME = '2015-05-12 22:14:18.003';
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, @a), 0);

(1 1900 .) . , :


2015-05-12 00:00:00.000

SQL Server , DATETIME.


, - :


DECLARE @a DATETIME = '2015-05-12 22:14:18.003';
SELECT DATEADD(DAY, DATEDIFF(DAY, '20010101', @a), '20010101');

, 1 2001 . . , . , :


DECLARE @a DATETIME = '2015-05-12 22:14:18.003';
SELECT DATEADD(MONTH, DATEDIFF(MONTH, '20010101', @a), '20010101');

, , DAY MONTH. :


2015-05-01 00:00:00.000

, DATEADD, , :


DECLARE @a DATETIME = '2015-05-12 22:14:18.003';
SELECT DATEADD(MONTH, DATEDIFF(MONTH, '20010101', @a), '20001231');

SELECT :


2015-04-30 00:00:00.000

DATE TIME, .


#7: DATEDIFF


DATEDIFF, . , . , /:


DECLARE 
  @a DATETIME2(3) = '2015-12-31 23:59:59.000',
  @b DATETIME2(3) = '2016-01-01 00:00:00.000';
SELECT DATEDIFF(mi, @a, @b);

SELECT 1, , , .


, :


DECLARE 
  @a DATETIME2(3) = '2015-12-31 23:59:59.000',
  @b DATETIME2(3) = '2016-01-01 00:00:00.000';
SELECT DATEDIFF(hh, @a, @b);

SELECT , , .


:


DECLARE 
  @a DATETIME2(3) = '2015-12-31 23:59:59.000',
  @b DATETIME2(3) = '2016-01-01 00:00:00.000';
SELECT DATEDIFF(mm, @a, @b);

, . , . .


DATEDIFF, , , , . SQL Server ( , , ), . , , . , SQL Server .


— , . , . , 60:


DECLARE 
  @a DATETIME2(3) = '2015-12-31 23:59:59.000',
  @b DATETIME2(3) = '2016-01-01 00:00:00.000';
SELECT DATEDIFF(ss, @a, @b)/60.0000;

SELECT 0.0166666, 1, .


#8:


— . , , , - .


, , , DATETIME2:


CREATE TABLE #a (ColA INT, ColB DATETIME2(3));
INSERT INTO #a VALUES
  (101, '2015-05-06 22:43:55.123'),
  (102, '2015-05-06 23:59:59.997'),
  (103, '2015-05-07 00:00:00.000'),
  (104, '2015-05-07 17:33:36.321'),
  (105, '2015-05-08 00:00:00.000'),
  (106, '2015-05-08 10:18:12.987');

7 2015 :


SELECT ColA, ColB FROM #a
WHERE ColB = '2015-05-07';

, , :


ColAColB
1032015-05-07 00:00:00.000

, DATETIME2, /, . ( ). , , , SQL Server (00:00:00) .


, , , , , .


— , :


SELECT ColA, ColB FROM #a
WHERE CONVERT(CHAR(8), ColB, 112) = '20150507';

SELECT , :


ColAColB
1032015-05-07 00:00:00.000
1042015-05-07 17:33:36.321

, , , SQL Server . SQL Server , /.


- BETWEEN:


SELECT ColA, ColB FROM #a
WHERE ColB BETWEEN '2015-05-06' AND '2015-05-08';

, , , :


ColAColB
1012015-05-06 22:43:55.123
1022015-05-06 23:59:59.997
1032015-05-07 00:00:00.000
1042015-05-07 17:33:36.321
1052015-05-08 00:00:00.000

, BETWEEN . , :


SELECT ColA, ColB FROM #a
WHERE ColB BETWEEN '2015-05-07' AND '2015-05-07';

SELECT :


ColAColB
1032015-05-07 00:00:00.000

WHERE ColB = '2015-05-07'. WHERE , WHERE, , :


WHERE ColB BETWEEN '2015-05-07 00:00:00.000' AND '2015-05-07 00:00:00.000';

, . , , .


SELECT ColA, ColB FROM #a
WHERE ColB BETWEEN '2015-05-07' AND '2015-05-07 23:59:59.999';

SELECT , :


ColAColB
1032015-05-07 00:00:00.000
1042015-05-07 17:33:36.321

DATETIME2, , :


SELECT ColA, ColB FROM #a
WHERE ColB >= '2015-05-07' AND colB < '2015-05-08';

SELECT , . DATETIME.


, ColB DATETIME :


CREATE TABLE #a (ColA INT, ColB DATETIME);
INSERT INTO #a VALUES
  (101, '2015-05-06 22:43:55.123'),
  (102, '2015-05-06 23:59:59.997'),
  (103, '2015-05-07 00:00:00.000'),
  (104, '2015-05-07 17:33:36.321'),
  (105, '2015-05-08 00:00:00.000'),
  (106, '2015-05-08 10:18:12.987');

BETWEEN :


SELECT ColA, ColB FROM #a
WHERE ColB BETWEEN '2015-05-07' AND '2015-05-07 23:59:59.999';

SELECT :


ColAColB
1032015-05-07 00:00:00.000
1042015-05-07 17:33:36.320
1052015-05-08 00:00:00.000

, BETWEEN DATETIME . SQL Server , , DATETIME. , , , . , :


SELECT ColA, ColB FROM #a
WHERE ColB >= '2015-05-07' AND colB < '2015-05-08';

, , :


ColAColB
1032015-05-07 00:00:00.000
1042015-05-07 17:33:36.320

, /.


#9: /


, - . / , .


:


DECLARE 
  @a DATETIME2 = '1623-01-01',
  @b DATETIME  = NULL;
SET @b = @a;
SELECT @b;

DATETIME2 1623- DATETIME. , DATETIME 1753 9999. , . , , , , :


Msg 242, Level 16, State 3, Line 792
The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.

Msg 242, Level 16, State 3, Line 792
   datetime2    datetime       .

. DATETIME 1623 .


SMALLDATETIME :


DECLARE 
  @a DATETIME      = '1823-01-01',
  @b SMALLDATETIME = NULL;
SET @b = @a;
SELECT @b;

SELECT "out-of-range", SMALLDATETIME 1900 2079. .


#10:


SQL Server 2008 , . , , GETDATE GETUTCDATE.


:


SELECT GETDATE();
SELECT SYSDATETIME();
SELECT GETUTCDATE();
SELECT SYSUTCDATETIME();
SELECT SYSDATETIMEOFFSET();

, , :


2015-05-14 10:28:21.700
2015-05-14 10:28:21.7030000
2015-05-14 17:28:21.700
2015-05-14 17:28:21.7030000
2015-05-14 10:28:21.7030000 -07:00

SYSDATETIME DATETIME2. SYSUTCDATETIME , UTC-. SYSDATIMEOFFSET DATETIMEOFFSET, UTC-.


SQL Server 2008 DATENAME DATEPART /. : , UTC-. , DATENAME:


DECLARE @a DATETIMEOFFSET = '2015-05-12 19:14:38.9046722 -07:00';
SELECT 
  DATENAME(mcs, @a) AS Microseconds,
  DATENAME(ns, @a) AS Nanoseconds,
  DATENAME(tz, @a) AS TimezoneOffset;

:


MicrosecondsNanosecondsTimezoneOffset
904672904672200-07:00

DATEPART :


DECLARE @a DATETIMEOFFSET = '2015-05-12 19:14:38.9046722 -07:00';
SELECT 
  DATEPART(mcs, @a) AS Microseconds,
  DATEPART(ns, @a) AS Nanoseconds,
  DATEPART(tz, @a) AS TimezoneOffset;

:


MicrosecondsNanosecondsTimezoneOffset
904672904672200-420

DATENAME, , , .


/, /, /.


. : "Parameter sniffing SQL Server: ".

Source: https://habr.com/ru/post/undefined/

More articles:


All Articles