Error converting data type varchar to datetime

How can I solve the error in the following procedure?

CREATE PROCEDURE cards
    @salesman VARCHAR(10),
    @RCV10 INT,
    @RCV09 INT,
    @RCV15 INT,
    @GPRS15 INT,
    @RCV20 INT,
    @RCV25FTT INT,
    @RCV25 INT,
    @RCV31 INT,
    @RCV30 INT,
    @RCV35 INT,
    @RCV50 INT,
    @RCV55 INT,
    @SIM INT,
    @VTOPSIM INT,
    @VTOPBAL INT,
    @THREEGSIM INT,
    @entrydate DATETIME
AS
BEGIN
    IF EXISTS(
           SELECT *
           FROM   CardsIssued
           WHERE  salesman = @salesman
                  AND RCV10 > @RCV10
                  AND RCV09 > @RCV09
                  AND RCV15 > @RCV15
                  AND GPRS15 > @GPRS15
                  AND RCV20 > @RCV20
                  AND RCV25FTT > @RCV25FTT
                  AND RCV25 > @RCV25
                  AND RCV31 > @RCV31
                  AND RCV30 > @RCV30
                  AND RCV35 > @RCV35
                  AND RCV50 > @RCV50
                  AND RCV55 > @RCV55
                  AND SIM > @SIM
                  AND VtopSim > @VTOPSIM
                  AND VtopBal > @VTOPBAL
                  AND ThreeGSim > @THREEGSIM
                  AND EntryDate = @entrydate
       )
    BEGIN
        INSERT Cards_Returned
        VALUES
          (
            @salesman,
            @RCV10,
            @RCV09,
            @RCV15,
            @GPRS15,
            @RCV20,
            @RCV25FTT,
            @RCV25,
            @RCV31,
            @RCV30,
            @RCV35,
            @RCV50,
            @RCV55,
            @SIM,
            @VTOPSIM,
            @VTOPBAL,
            @THREEGSIM,
            @EntryDate
          )
    END
    ELSE
        PRINT'CARDS RETURNED CANNOT BE GREATER THAN CARDS ISSUED'
END

Run as:

execute cards 'S001',50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,50,'28/1/2013'

gives the following error:

Msg 8114, Level 16, State 5, Procedure cards, Line 0
Error converting data type varchar to datetime

• Question

• I do not know what might be wrong with my Stored Proc as I got this error. I will explain by creating a temp table.

  CREATE TABLE #Value
  (CustID     INT Not Null IDENTITY(1,1) Primary Key
  , CustName  NVARCHAR(20) Not Null
  , Product   NVARCHAR(40) Null
  , ShipDate  DATETIME NOT Null DEFAULT ''
  , DelDate   DATETIME NOT Null DEFAULT ''
  , Price     MONEY    NULL)
  
  
  INSERT INTO #Value (CustName,Product,ShipDate,DelDate,Price)
  VALUES ('Hannah','Bag','20090116','20090210','23.1')
       , ('Hannah','Novel','20090124','20090220','3.1')
  	 , ('Hannah','Shoes','20090210','20090210','25.20')
  	 , ('Hannah','Rings','20090120','','11')
  	 , ('Kayla','iPad','20111110','20111210','499')
  	 , ('Kayla','Bag','20110422','20110501','30')
  	 , ('Kayla','iPhone4S','','20110901','320')
  	 , ('Jack','Nokia393','20050312','20050501','12')
  	 , ('Kelly','USB Stick','20110312','20110501','14')
  	 , ('Cain','Wig','20110312','20110601','12')
  	 , ('Kate','Wig','20110312','20110314','3')
  	 , ('Kelly','Laptop','20110312','20110316','510')
  	 , ('Kelly','DVD','20110312','20110318','9');

  And then I wrote the below query against it.

  CREATE PROC #YearMonthParameter
  (
        @Year DATETIME 
  	, @Month DATETIME 
  )
  AS
  SELECT @Year = DATENAME(YEAR,GETDATE())
       , @Month = DATENAME(MONTH,GETDATE())
  
  BEGIN
  SELECT CustName 
       , Product
  	 , ShipDate 
  	 , DelDate 
  	 , Price 
  FROM #Value 
  WHERE DATENAME(YEAR,DelDate) = @Year
  AND DATENAME(Month,DelDate) = @Month 
  END
  GO

  EXEC #YearMonthParameter '2009','February'

  Error converting data type varchar to datetime.

  The above was the error I got after execution.

  The logic is to pass a MONTH and a Year parameters to fetch the data.

  I will appreciate your help on this. Thank you

  ---

  Zionlite

  • Edited by

    Friday, November 8, 2013 5:25 PM

I do not understand why the data is being converted from varchar to datetime when ‘Created’ is set to datetime

The literals you are providing for comparison to the Created column are strings. To compare those literals with the datetime column, SQL Server attempts to convert the strings to datetime types, according to the rules of data type precedence. Without explicit information about the format of the strings, SQL Server follows its convoluted rules for interpreting strings as datetimes.

In my view, the neatest way to avoid these types of issues is to be explicit about types. SQL Server provides the CAST and CONVERT functions for this purpose. When working with strings and date/time types, CONVERT is to be preferred because it provides a style parameter to explicitly define the string format.

The question uses strings in ODBC canonical (with milliseconds) format (style 121). Being explicit about the data type and string style results in the following:

SELECT COUNT(*)
FROM dbo.profile 
WHERE [Created] BETWEEN 
    CONVERT(datetime, '2014-11-01 00:00:00.000', 121)
    AND 
    CONVERT(datetime, '2014-11-30 23:59:59.997', 121);

That said, there are good reasons (as Aaron points out in his answer) to use a half-open range instead of BETWEEN (I use style 120 below just for variety):

SELECT COUNT(*)
FROM dbo.profile 
WHERE
    [Created] >= CONVERT(datetime, '2014-11-01 00:00:00', 120)
    AND [Created] < CONVERT(datetime, '2014-12-01 00:00:00', 120);

Being explicit about types is a very good habit to get into, particularly when dealing with dates and times.

Error converting data type varchar to datetime2

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

I do not know what might be wrong with my Stored Proc as I got this error. I will explain by creating a temp table.

And then I wrote the below query against it.

The above was the error I got after execution.

The logic is to pass a MONTH and a Year parameters to fetch the data.

I will appreciate your help on this. Thank you

Answers

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

You declare @Year and @Month as datetime, but then you pass the value ‘2009’ and ‘February’. So SQL tries to convert ‘February’ to a datetime. That is an illegal conversion. @Year and @Month should be declared as varchar( ).

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

You declared the input paramters to the stored procedure as datetime and you are passing the strings when you executed the proecudure.

Either you pass the correct date format when you call the procedure

change the input parameters to the stored procedure as varchar type and remove the select statement that gets the month and year values as you are already passing the month and year in this case.

Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer

Источник

Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in TSQL

In my T-SQL select there appears to be an error with my datetime

The error message is German and it says:

Meldung 242, Ebene 16, Status 3, Zeile 1
Bei der Konvertierung eines nvarchar-Datentyps in einen datetime-Datentyp liegt der Wert außerhalb des gültigen Bereichs.

I tried to translate this to:

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.»

I really don’t know how I did from so thanks for any help guys

1 Answer 1

Your problem is triggered by cast(t.F9 as datetime) .

Please do : SELECT getdate(); to get the implicit «datetime to string» convertion format.

WARNING : Implicit convertion format are set at the instance level. It can differ from a server to another, even in the same compagny.

This will gives you someting like dd.MM.yyyy HH:mm:ss or yyyy-MM-dd HH:mm:ss or .

The given format is the one expected and required for all F9 of TRANS6 table records!!

A single TRANS6.F9 with a wrong formating patern will raise this ERROR. So analyse your F9 data, find the concerned rows, clean them and retry.

NOTE : CONVERT(NVARCHAR(8), getdate(),112) get a string like YYYYMMDD (Ex : ‘20170928’) witch is the only sortable string format of dates.

EDIT : F9 = ‘2016.10.30’ and Implicit convertion expect ‘2016-10-30’ !!

Источник

«Error converting data type varchar to datetime»

How can I solve the error in the following procedure?

gives the following error:

6 Answers 6

’28/1/2013′ is an ambiguous format — SQL Server might interpret it as dd/mm/yyyy or as mm/dd/yyyy . In this case, it’s done the latter, and doesn’t know what the 28th month of the year is.

Use ‘20130128’ instead. This is always interpreted as yyyymmdd .

Had the same error. In my case it was passing datetime2 value to a datetime column:

Had to change string to datetime (3 digits) instead of 7 digits for milliseconds:

As per your error message, problem is with the date format . Format your date to ISO format (yyyymmdd) before execution as below.

perhaps try changing the input variable for @EntryDate to a varchar. Then, when using it further down, perform a CONVERT(datetime,@EntryDate) on it.

That would be datetime conversion 103 (dd/mm/yyyy):

Some more commentary .. and a different solution..

SQL Server Profiler is giving me dates as per example :

Related question here:

SQL Server profiler seems to do this different date format for exec but not INSERT (for example) ..

It seems that the solution to the SQL Server Profiler date values in exec statements is to add in the T in the date (manually).

Источник

Ошибки при работе с датой и временем в SQL Server

Перевод статьи подготовлен специально для студентов курса «MS SQL Server разработчик».

Ошибка #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 :

Как мы и ожидали, оператор SELECT возвращает значение в следующем виде:

Чтобы получить значение, которое действительно хранится, мы должны преобразовать его в тип VARBINARY :

Неудивительно, что результат теперь выглядит совсем по-другому:

Поскольку SQL Server хранит значение DATETIME в виде двух целых чисел (int), то его размер составляет 8 байт (каждое число 4 байта). Первые 4 байта (0000A491) представляют собой дату, а последние 4 байта (00A6463C) время. Зная это, мы можем использовать функцию SUBSTRING , чтобы посмотреть только дату или только время:

Теперь оператор SELECT возвращает только байты, представляющие дату:

Можем то же самое сделать для времени и преобразовать VARBINARY в INT . Давайте соберем все вместе и посмотрим как хранится исходное значение DATETIME :

В следующей таблице показаны результаты SELECT:

DateBinary	DateInt	TimeBinary	TimeInt
0x0000A491	42129	0x00A6463C	10896956

Результаты показывают, что с 1 января 1900 года прошло 42 129 дней, а с полуночи прошло более 10 миллионов долей секунды.

Теперь давайте переведем часы примерно на 188 лет назад :

На этот раз до 1 января 1900 года было 26 327 дней, а время — более 24 миллионов тиков:

DateBinary	DateInt	TimeBinary	TimeInt
0xFFFF9929	-26327	0x016EB86D	24033389

Теперь установим дату и время по умолчанию (день 0):

Как и ожидалось, VARBINARY и INT содержат ноль:

DateBinary	DateInt	TimeBinary	TimeInt
0x00000000		0x00000000

Теперь, просто убедиться, что вы поняли, посмотрим еще один пример с DATETIME . К значению по умолчанию прибавляем один день и одну секунду:

На данный момент результаты должны быть вполне ожидаемыми. Дата — 1, время — 300:

DateBinary	DateInt	TimeBinary	TimeInt
0x00000001	1	0x0000012C	300

Сейчас у вас должно быть довольно хорошее представление о том, как хранятся значения DATETIME . Однако, для других типов даты/времени SQL Server использует несколько иной подход. Давайте посмотрим на тип данных DATETIME2 , объявленный с точностью по умолчанию (7):

На этот раз наши результаты выглядят немного иначе, чем для DATETIME :

Для DATETIME2 SQL Server использует первый байт для хранения точности времени (07), последние три байта для хранения даты (EC390B), и все что между ними для хранения времени (B4854E9254), длина которого может изменяться в зависимости от указанной точности. Типы данных DATE и TIME работают аналогично. Например, сохраним это же значение в DATE :

Наши результаты совпадают с частью даты, возвращенной в предыдущем примере:

И то же самое для типа TIME :

Результаты совпадают с частью времени, возвращенной в примере с DATETIME2 :

Теперь изменим точность и значение времени:

Оператор SELECT возвращает следующие результаты:

Обратите внимание, что первый байт хранит точность (04), после него идет уже меньшее количество байтов, связанных со временем (01000000). К сожалению, логика, которую SQL Server использует для хранения даты и времени для типов DATETIME2 , DATE и TIME не так проста, как для DATETIME , и углубление в эту логику выходит за рамки данной статьи, но, по крайней мере, вы можете увидеть отдельно байты, представляющие дату и время, и получить некоторое представление о том, что происходит.

Смысл всего этого не в том, чтобы запомнить точные механизмы SQL Server, используемые для хранения даты и времени, а скорее для общего понимания того, как хранятся даты и что это не просто строки определенного формата. Понимание этого поможет вам лучше решать различные вопросы, возникающие при работе с датой/временем.

Ошибка #2: Забыть о людях, которые живут в других частях света

T-SQL может быть воспринят как универсальный язык, по крайней мере, в рамках базы данных, однако, это не касается параметров SQL Server. Довольно часто установленный экземпляр сконфигурирован так, чтобы обслуживать только локальных (местных) пользователей. Но это часто приводит к проблемам при работе с датой/временем. Хотя SQL Server хранит даты в виде одного или нескольких целых чисел, но за кулисами он часто преобразует их из целых чисел в строковые читаемые форматы и наоборот, чтобы нам не приходилось работать с датами, которые выглядят как 15481099 или как 24033389.

Для этого в SQL Server есть несколько параметров и правил, которые определяют как интерпретировать строковые значения даты/времени. Давайте посмотрим несколько примеров. В первом случае мы установим язык british (британский английский) и преобразуем значение VARCHAR в DATETIME :

Как и ожидалось, оператор SELECT возвращает следующие результаты:

Теперь давайте установим язык на US English (американский английский) и попробуем преобразовать значение:

На этот раз SQL Server возвращает ошибку:

Проблема здесь в формате даты: день-месяц-год. Это прекрасно работает, когда SQL Server сконфигурирован для языка british, но не работает для US English. Когда SQL Server, настроенный на US English, видит данные, он предполагает, что мы пытаемся передать значение 19 как месяц, а не как день. Мы можем решить эту проблему, подставив значение, которое больше соответствует ожиданиям американцев:

Теперь наш SELECT сработает отлично. Если мы изменим язык обратно на british и подставим то же значение, мы снова получим ошибку «out-of-range».

Конечно, мы могли бы изменить язык еще раз, но это не очень эффективное, если мы разрабатываем интернациональное приложение. Лучше будет, если наши значения даты/времени будут универсальными по своей природе, например, используя формат «год-месяц-день«. Например, предположим, что мы пытаемся передать значение даты/времени в данном формате с дефисами:

Этот формат считается более универсальным способом передачи данных времени и даты. В конце концов, именно в таком виде SQL Server возвращает данные. Для разделения компонент даты можно использовать тире, косую черту или точку, до тех пор, пока значения соответствуют структуре «год-месяц-день«. Однако, несмотря на универсальность формата, оператор SELECT снова возвращает ошибку:

Но посмотрите, что произойдет, если мы будем придерживаться того же языка и того же формата, но на этот раз преобразуем данные в DATETIME2 :

Теперь SELECT без проблем преобразует дату и возвращает следующий результат:

Оказывается, что формат «год-месяц-день» все еще зависит от настроек SQL Server, когда речь идет о типе данных DATETIME , но не DATETIME2 .

Если мы придерживаемся типа данных DATETIME2, то можем избежать языковой проблемы при использовании формата «год-месяц-день«, который является лучшим вариантом, при работе с SQL Server 2008 или более поздним. Но не у всех есть такая роскошь. Нам нужен формат, который будет независимым от типа и языка. По этой причине многие разработчики по умолчанию используют такой формат, как ISO 8601:

На этот раз выражение выполняется без ошибок. Используя универсальный формат для значения времени/даты, мы можем лучше гарантировать, что мы получим желаемые результаты, независимо от того где мы находимся — в Сиэтле, Кембридже или Риме.

Ошибка #3: Снова забыть о людях, которые живут в других частях света

Форматы даты — не единственное, где мы можем попасть в неприятности, когда разрабатываем приложение, которое охватывает несколько географических регионов. Также могут возникнуть проблемы с часовыми поясами, если потребуется отслеживать данные за периоды времени по нескольким регионам.

Одна из проблем заключается в том, что большинство типов даты/времени в SQL Server довольно неоднозначны. Например, у нас есть таблица, которая отслеживает события, связанные с безопасностью и одна из строк показывает событие, произошедшее 15 мая 2015 года в 3:30 утра. Это время на локальной машине? Или это время сервера? Настроен ли SQL Server, чтобы использовать время, отличное от местного? Является ли это значением в UTC? Без какого-либо механизма, обеспечивающего контекст, это значение почти бессмысленно.

Во многих случаях это не проблема, но для систем мониторинга или для критических процессов, точное время имеет важное значение. Представьте себе, если был взломан международный банк, деньги украдены со счетов, а у следователей нет возможности узнать, когда точно произошло это событие. А как насчет аналитики с учетом времени? Анализ исторических данных может стать сложным, если данные охватывают часовые пояса и нет механизма обеспечения контекста, такого как часовой пояс, в котором были введены данные.

Даже если такие механизмы и есть, реализация решений, учитывающих часовые пояса, часто зависит от разработчиков. Без единого подхода для разных приложений (а иногда и в рамках одного приложения).

К счастью, в SQL Server 2008 появился тип данных DATETIMEOFFSET , который должен сделать управление датой/временем немного проще. Этот тип хранит данные аналогично DATETIME2 с дополнительной парой байт, используемой для часового пояса (относительно UTC).

Рассмотрим следующий пример:

Системная функция SYSDATETIMEOFFSET возвращает текущую дату и время в виде DATETIMEOFFSET , что означает, что оно включает в себя дату, время и значение UTC-смещения:

В этом случае значение даты/времени отстает от UTC на семь часов, и мы окажемся на западном побережье США. Для получения только значения смещения, то можем использовать функцию DATENAME :

Как и ожидалось, SELECT возвращает только разницу с UTC:

Далее мы можем продемонстрировать, как работает тип данных DATETIMEOFFSET сравнивая его с UTC-аналогом:

Как показывают следующие результаты, дата и время UTC на семь часов опережают Тихоокеанскую (PDT) дату и время.

UTC	PDT
2015-05-09 00:57:37.1820000 +00:00	2015-05-08 17:57:37.1820000 -07:00

В SQL Server 2008 была добавлена системная функция SWITCHOFFSET для изменения значения DATETIMEOFFSET на другой часовой пояс:

В данном случае мы просто изменяем значение UTC-смещения с -07:00 на -05:00 при получении данных:

Очевидно, что SQL Server значительно облегчил работу с часовыми поясами, и нет причин не пользоваться этими возможностями, если вы используете SQL Server 2008 и старше. Однако существует проблема, которую SQL Server не смог решить — переход на летнее и зимнее время.

Давайте посмотрим, что происходит, когда мы сравниваем часовые пояса между Мельбурном и Сиэтлом на 1 апреля 2015 года:

На данный момент и Мельбурн, и Сиэтл находятся на летнем времени, давая нам 18-часовую разницу между датами. Однако давайте сравним 1 мая 2015 года, используя те же значения смещения UTC:

Мы снова видим разницу в 18 часов, хотя на самом деле должно быть 17, потому что Мельбурн вернулся к зимнему времени 5 апреля. Значение смещения, которое мы должны были использовать 5-го мая для Мельбурна, составляет +10:00.

Несмотря на то, что это очень простой пример, он показывает на большую проблему, связанную с отслеживанием летнего и зимнего времени по всему миру. Не во всех регионах происходит смена летнего и зимнего времени. А там, где происходит, то она не всегда бывает в одно и то же время. Кроме того, даты начала и окончания меняются от года к году. И эти даты становятся еще менее предсказуемыми из-за того, что изменяются законы, определяющие переход на летнее/зимнее время.

Добавьте к этому еще тот факт, что сами часовые пояса могут сильно различаться даже в пределах региона или страны. Например, штат Аризона в США. Большая часть штата не меняет время. Это означает, что часть года они синхронизированы с Колорадо, а остальную часть года живут как в Калифорнии.

Все это указывает на то, что вы не можете полагаться только на DATETIMEOFFSET для обеспечения надежности ваших значений даты/времени. Изменение часовых поясов и неуправляемый переход на летнее/зимнее время делают работу с часовыми поясами сложной задачей без наличия простого решения. Если ваши приложения и аналитика полагаются на точное измерение даты и времени, то приложение должно содержать логику, необходимую для обработки таких ситуаций.

Ошибка #4: Относиться к DATETIME2 только как к более точному DATETIME

Несмотря на то, что DATETIME2 появился еще в SQL Server 2008, многие разработчики не спешат его использовать и используют DATETIME по привычке, а не по каким-то другим причинам. Но, помимо большей точности, DATETIME2 имеет еще другие преимущества перед DATETIME .

Давайте начнем с того, что посмотрим на них в действии:

Тип данных DATETIME2 поддерживает до семи десятичных знаков после запятой для компонента времени, тогда как DATETIME только три. Это дает нам следующие результаты.

DateTime2Type	DateTimeType
2015-05-12 09:47:12.4556789	2015-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:

Как видно, значение DATETIME2 теперь включает только три десятичных разряда, так же как и DATETIME .

DateTime2Type	DateTimeType
2015-05-12 09:47:12.556	2015-05-12 09:47:12.557

Еще раз, часть времени DATETIME2 округляется, потому что представленное значение превысило указанную точность, но даже это округление является более точным, чем то, которое мы получаем с DATETIME . Хотя оба значения занимают три знака после запятой, SQL Server использует 7 байт для хранения DATETIME2 и 8 байт для DATETIME .

Фактически DATETIME2 использует 8 байт при точности больше 4, и только 6 байт, если точность меньше 3. C DATETIME2 вы можете получить не только большую точность, но и сэкономить место, что может быть важным работе с большими объемами данных. Тип DATETIME2 также позволяет полностью удалить знаки после запятой:

Возможность убрать знаки после запятой — это хорошо, когда вам не нужны микросекунды. Теперь наши результаты стали чище:

DateTime2Type	DateTimeType
2015-05-12 09:47:13	2015-05-12 09:47:12.557

Еще один важный момент при сравнении DATETIME2 и DATETIME это то, что DATETIME2 поддерживает гораздо более широкий диапазон дат (с 1 января 0001). В то время как DATETIME только с 1 января 1753 года. (Оба они ограничены 9999 годом, что, я уверен, многих успокоит).

Стоит также сказать пару слов и о типах DATE и TIME , которые предоставляют такую же точность.

Как вы можете видеть, типы DATE и TIME являются хорошим дополнением для удобной работы с датой/временем:

DateType	TimeType
2015-05-12	09:47:12.5556789

Конечно, в старые приложения и системы не всегда просто внедрить новые типы данных, но при создании новых систем нет причин не использовать эти типы (если только вы не работаете с версиями SQL Server меньше 2008 или с технологиями, которые не могут обрабатывать значения DATETIME2 ).

У типа DATETIME2 и других новых типов есть много преимуществ, чтобы их игнорировать, в том числе лучшее соответствие типам даты/времени .NET. И как было отмечено ранее, тип DATETIME2 более снисходителен к форматам даты/времени, которые вы передаете в базу данных. Очевидно, что пришло время избавиться от старых привычек и отказаться от DATETIME .

Ошибка #5: Игнорировать округление даты/времени

В предыдущем разделе мы затронули вопрос округления, но эта тема заслуживает большего внимания. Особенно в том, что касается DATETIME и SMALLDATETIME . Сначала давайте посмотрим, что произойдет, если мы округлим DATETIME2 :

Здесь точность DATETIME2 по умолчанию равна 7 — это количество знаков после запятой для секунд. Как видно из результатов, значение @a никак не округляется, а @b и @c округляются:

OrigValue	StoredValue
2015-05-12 23:32:12.1234567	2015-05-12 23:32:12.1234567
2015-05-12 23:32:12.123456789	2015-05-12 23:32:12.1234568
2015-05-12 23:59:59.999999999	2015-05-13 00:00:00.0000000

Значение @b округляется, как мы и ожидали: девять цифр сокращаются до семи цифр, а значение 123456789 округляется до 1234568 . Значение @c также подчиняется аналогичной логике. Однако, так как мы округляем вверх, то переходим к следующему дню. В обоих случаях SQL Server работает вполне предсказуемо. Хотя существует вероятность того, что значение будет увеличено до следующего дня, но оно все равно соответствует ожидаемому поведению.

Теперь давайте посмотрим, что происходит со значениями DATETIME :

Значение @a округляется в большую сторону, @b округляется в меньшую сторону, а @c переносится на следующий день:

OrigValue	StoredValue
2015-05-12 23:59:59.996	2015-05-12 23:59:59.997
2015-05-12 23:59:59.998	2015-05-12 23:59:59.997
2015-05-12 23:59:59.999	2015-05-13 00:00:00.000

Что удивительно в этом округлении, так это то, что значения, которые мы передаем, не превышают точности DATETIME , но округление все равно происходит. Как говорилось ранее, SQL Server хранит данные в DATETIME с шагом .000, .003 и .007 секунд. Это может стать проблемой с аналитическими отчетами, требующих высокой точности. И это еще более проблематично, когда мы не можем присвоить точное значение, потому существует вероятность того, что оно будет округлено до следующего дня.

Вероятность потерять день может показаться незначительной, но это может произойти неожиданным образом. Например, предположим, что мы хотим преобразовать значение DATETIME2 в значение DATETIME :

Поскольку исходная точность превышает то, с чем DATETIME может справиться, то происходит округление с переходом на следующий день:

Datetime2Value	DatetimeValue
2015-05-12 23:59:59.9986789	2015-05-13 00:00:00.000

Но мы можем столкнуться с еще более запутанными проблемами SMALLDATETIME :

Несмотря на то, что возвращаемые значения всегда показывают «00» секунд, точность у SMALLDATETIME только до ближайшей минуты,

OrigValue	SmalldatetimeValue
2015-05-12 23:22:22	2015-05-12 23:22:00
2015-05-12 23:22:30	2015-05-12 23:23:00
2015-05-12 23:22:52	2015-05-12 23:23:00
2015-05-12 23:59:52	2015-05-13 00:00:00

Округление значения @a довольно просто: эти 22 секунды округляются в меньшую сторону, поэтому значение минуты остается неизменным. Значение @b округляется в большую сторону, так как SQL Server округляет 30 секунд и более до следующей минуты. Это также относится и к значению @c . Однако, значение @d переходит на следующий день, потому что 59 минут также округляются в большую сторону, что привело к тому, что 23 часа перешли на следующий день.

Теперь посмотрим, что произойдет, если мы добавим доли секунды:

И снова мы переходим на следующий день:

Вы должны быть осторожны, когда речь идет о типах DATETIME и SMALLDATIME , так как можете получить данные, которые будут неожиданным образом влиять на ваши результаты. Если есть возможность использовать DATETIME2 , то используйте его.

Ошибка #6: Делать лишнюю работу для удаления времени из полной даты

Часто вас интересует только день без времени и вы хотите обнулить время или совсем избавиться от него. До SQL Server 2008 приходилось немного потрудиться, чтобы получить только дату, но теперь есть тип DATE , который делает нашу жизнь проще:

Здесь мы просто преобразуем значение DATETIME2 в значение DATE , и все работает отлично:

Мы можем также легко преобразовать значение DATETIME в значение DATE и получить такие же результаты:

Также можем преобразовать наше исходное значение в тип TIME :

Как и ожидалось, SELECT теперь возвращает только время:

Прежде чем мы продолжим, обязательно обратите внимание на типы данных DATE и TIME . Если в вашей конкретной ситуации они вам доступны, то используйте их и забудьте об остальной части этого раздела. Эти типы являются лучшими способами для получения даты из значений даты со временем.

Однако если у вас нет такой возможности, тогда вы должны искать другие решения. Один из вариантов заключается в следующем:

Как видите, мы преобразовываем дату сначала в строку, используя формат ISO (112), а затем обратно в DATETIME , и получаем следующий результат:

Хотя это решение работает, но оно не сделает SQL Server счастливым. Для одной или двух строк это не проблема, но представьте, если вы конвертируете миллионы строк.

Решением получше будет использование системных функций DATEADD и DATEDIFF для обнуления этих дат:

Здесь мы вычисляем количество дней между нулевым днем (1 января 1900 г.) и нашей датой. А затем добавляем разницу к нулевому дню, получая следующий результат:

В этом случае SQL Server будет счастлив, потому что он сможет воспользоваться целочисленной природой DATETIME .

Аналогичный подход заключается в том, чтобы вместо нулевого дня указать какую-то другую дату:

Здесь мы также получаем нужный нам результат, вычисляя разницу между 1 января 2001 г. и указанной датой. Самое интересное в этом подходе то, что мы можем использовать его немного для других целей. Например, мы можем получить первый день месяца для указанной даты:

Все, что мы сделали, это изменили аргумент DAY на MONTH . И получили следующий результат:

Если мы изменим дату, указанную в функции DATEADD , на один день раньше нашей базовой даты, мы можем получить последний день предыдущего месяца:

Теперь SELECT возвращает следующие результаты:

Даже если вам доступны типы DATE и TIME , то эти два последних примера могут быть удобными для получения необходимых вам данных.

Ошибка #7: Не понимать как работает функция DATEDIFF

Раз уж мы заговорили о функции DATEDIFF , то стоит присмотреться к ней повнимательнее. Если мы не будем осторожны при ее использовании, то можем получить странные результаты. Например, пытаемся определить количество минут между двумя значениями даты/времени:

В этом случае SELECT возвращает значение 1, то есть целую минуту, несмотря на то, что между этими датами разница всего в одну секунду.

То же самое произойдет, если мы попытаемся найти разницу в часах:

На этот раз SELECT показывает, что разница между значениями составляет один час, а не одну секунду.

То же самое происходит с месяцами:

Опять же, мы получаем один месяц. Если будем искать годы, то получим аналогичные результаты. Но мы все еще говорим только об одной секунде.

Проблема не в самой функции DATEDIFF , а, скорее, в нашем понимании того, как она работает. SQL Server смотрит не дальше той части даты (будь то год, месяц, час или минута), которую мы указали. Поэтому если указать месяц, то сравниваются годы и месяцы, но не более. Значения могут отличаться всего на одну секунду, но SQL Server заботят только годы и месяцы.

Один из способов это обойти — опуститься на один уровень глубже, чем нам нужно. Например, мы хотим получить количество минут между двумя значениями. Вместо этого мы можем получить количество секунд, а затем разделить на 60:

Теперь SELECT возвращает 0.0166666, а не 1, что гораздо ближе к истине.

Ошибка #8: Небрежно относиться к условиям поиска

Использование даты и времени в условиях поиска — дело тонкое и надо быть осторожным. Не проявляя должной внимательности, вы можете получить неправильные данные или, что еще хуже, что-то изменить или удалить по ошибке.

Чтобы продемонстрировать, почему это может быть проблемой, давайте создадим временную таблицу и заполним ее несколькими строками, которые содержат DATETIME2 :

Теперь попробуем выбрать строки на 7 мая 2015 года:

Как мы видим, наш запрос возвращает только одну строку, когда мы хотим видеть две:

ColA	ColB
103	2015-05-07 00:00:00.000

Проблема с получением данных таким образом заключается в том, что тип данных DATETIME2 , как и другие типы данных даты/времени, хранят и дату и время. Причем время часто отличается от полуночи (это когда все нули). Однако, когда мы сравниваем со значением, в котором хранится только дата без времени, то SQL Server использует полночь (00:00:00) для времени.

В результате, сравнение, подобное приведенному выше, отбрасывает все строки, кроме тех, которые соответствуют указанной дате и полуночи для времени.

Один из обходных путей — это преобразовать данные из таблицы в тип, соответствующий только дате:

Теперь SELECT возвращает результаты, которые мы хотим:

ColA	ColB
103	2015-05-07 00:00:00.000
104	2015-05-07 17:33:36.321

Проблема такого подхода заключается в том, что, как упоминалось ранее, SQL Server не любит таких преобразований. SQL Server не может ни эффективно использовать индексы, ни извлечь выгоду от целочисленной природы типов даты/времени.

Из-за этих проблем иногда обращаются к оператору BETWEEN :

На этот раз, однако, мы получаем больше строк, чем мы рассчитывали:

ColA	ColB
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

Проблема здесь в том, что оператор BETWEEN при поиске включает начальную и конечную даты. Давайте попробуем указать только дату, которая нас интересует:

И снова SELECT возвращает только одну строку:

ColA	ColB
103	2015-05-07 00:00:00.000

На этот раз проблема похожа на использование WHERE ColB = ‘2015-05-07’ . Оператор WHERE обрабатывает условие на основе полного значения даты со временем, поэтому наше условие WHERE, на самом деле, выглядит следующим образом:

Конечно, это условие соответствует только одной строке. Однако, мы можем решить эту проблему, если добавим время к конечной дате.

Теперь оператор SELECT возвращает результат, который мы хотим получить:

ColA	ColB
103	2015-05-07 00:00:00.000
104	2015-05-07 17:33:36.321

Хотя этот подход отлично работает для DATETIME2 , но мы можем упростить код, используя в условии операторы сравнения:

Оператор SELECT снова возвращает нужные нам результаты, сохраняя при этом наш запрос простым. Такой подход также является лучшим способом работы с типом DATETIME .

Предположим, что мы изначально определили ColB как DATETIME и заполнили таблицу:

Теперь давайте проверим BETWEEN с указанием точного времени:

SELECT возвращает три строки:

ColA	ColB
103	2015-05-07 00:00:00.000
104	2015-05-07 17:33:36.320
105	2015-05-08 00:00:00.000

Такой результат получается, потому что мы сравниваем даты в BETWEEN со значением DATETIME в таблице. SQL Server округляет значения, которые мы указали в условиях поиска, чтобы соответствовать точности DATETIME . Это приводит к тому, что наша конечная дата переносится на следующий день, и в выборку попадает строка, соответствующая этому дню. Чтобы обойти это, мы можем снова обратиться к операторам сравнения:

Теперь, как мы и ожидаем, возвращается две строки:

ColA	ColB
103	2015-05-07 00:00:00.000
104	2015-05-07 17:33:36.320

Итак, использование операторов сравнения в условиях поиска остается лучшим подходом независимо от используемого типа данных для времени/даты.

Ошибка #9: Забыть о диапазонах в типах данных для даты/времени

Это может показаться очевидным, но об этом все-таки стоит сказать. Если вы конвертируете данные из одного типа даты/времени в другой, то вы должны помнить о допустимых диапазонах дат.

Мы пытаемся преобразовать значение DATETIME2 с 1623-годом в значение DATETIME . К сожалению, тип данных DATETIME поддерживает только годы с 1753 по 9999. Хотя это хорошо для тех, кто смотрит в будущее. Но не для тех, кто увлекается историей или хочет выполнять запросы, аналогичные приведенному выше, который приводит к результатам, подобным следующему:

Сообщение довольно очевидно. Тип данных DATETIME не имеет никакого отношения к 1623 году.

Тип данных SMALLDATETIME еще более ограничен:

Оператор SELECT снова вернет ошибку «out-of-range», потому что тип данных SMALLDATETIME поддерживает только годы с 1900 по 2079. При преобразовании данных из одного типа в другой обязательно учитывайте эти ограничения.

Ошибка #10: Не использовать преимуществ функций работы с датой и временем

В SQL Server 2008 добавлены отличные встроенные функции для работы с датой и временем, и было бы стыдно не воспользоваться ими в полной мере. Некоторые, однако, откроют для себя новый мир за пределами GETDATE или GETUTCDATE .

Давайте посмотрим на некоторые функции даты и времени в действии:

Как видно из результатов, у нас есть множество вариантов, из которых можно выбрать:

Функция SYSDATETIME возвращает текущую дату и время в виде DATETIME2 . Функция SYSUTCDATETIME возвращает те же данные, но в виде UTC-значения. Функция SYSDATIMEOFFSET возвращает текущую дату и время как DATETIMEOFFSET , что означает также получение UTC-смещения.

В SQL Server 2008 также улучшены функции DATENAME и DATEPART для поддержки новых типов даты/времени. Теперь они включают в себя параметры для части даты: микросекунды, наносекунды и UTC-смещение. В следующем примере показаны эти возможности, используемые в функции DATENAME :

Microseconds	Nanoseconds	TimezoneOffset
904672	904672200	-07:00

Функция DATEPART работает практически так же:

Microseconds	Nanoseconds	TimezoneOffset
904672	904672200	-420

Результаты довольно похожи на DATENAME , за исключением смещения, отображаемого в минутах, а не в часах.

Вы должны быть знакомы со всеми функциями даты/времени, потому что они могут повысить вашу эффективность при работе с датой/временем, особенно если вы используете новые типы даты/времени.

На этом все. Ждем ваши комментарии и приглашаем на бесплатный вебинар на тему: «Parameter sniffing в SQL Server: что это и почему возникает».

Источник

Create Table of Expense

USE [AFMS]

GO

/****** Object: Table [dbo].[Expanse] Script Date: 01/03/2012 18:03:30 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Expanse](

[ExpanseId] [numeric](18, 0) IDENTITY(1,1) NOT NULL,

[ExpanseDate] [datetime] NOT NULL,

[Amount] [numeric](18, 0) NOT NULL,

[LNK_File_ID] [numeric](18, 0) NOT NULL,

[LNK_SubFile_ID] [numeric](18, 0) NULL,

[LNK_CreatedBy_ID] [numeric](18, 0) NOT NULL,

[CreatedOn] [datetime] NOT NULL,

CONSTRAINT [PK_Expanse] PRIMARY KEY CLUSTERED

(

[ExpanseId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Add Data

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

-- This script has been automatically generated by SQL Scripter 3.03

-- (unregistered and limited version)

-- http://www.sqlscripter.com

-- 3/1/2012 5:58:34 PM

-- 7 records

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

SET IDENTITY_INSERT [dbo].[Expanse] ON

IF NOT EXISTS (SELECT [ExpanseId] FROM [dbo].[Expanse] WHERE [ExpanseId] = 1)

INSERT INTO [dbo].[Expanse] ([ExpanseId], [ExpanseDate], [Amount], [LNK_File_ID], [LNK_SubFile_ID], [LNK_CreatedBy_ID], [CreatedOn])

VALUES (1, '20111230', 100, 1, 4, 4, '20111230')

ELSE

UPDATE [dbo].[Expanse] SET [ExpanseDate] = '20111230', [Amount] = 100, [LNK_File_ID] = 1, [LNK_SubFile_ID] = 4, [LNK_CreatedBy_ID] = 4, [CreatedOn] = '20111230' WHERE [ExpanseId] = 1

IF NOT EXISTS (SELECT [ExpanseId] FROM [dbo].[Expanse] WHERE [ExpanseId] = 2)

INSERT INTO [dbo].[Expanse] ([ExpanseId], [ExpanseDate], [Amount], [LNK_File_ID], [LNK_SubFile_ID], [LNK_CreatedBy_ID], [CreatedOn])

VALUES (2, '20111230', 100, 1, 4, 4, '20111230')

ELSE

UPDATE [dbo].[Expanse] SET [ExpanseDate] = '20111230', [Amount] = 100, [LNK_File_ID] = 1, [LNK_SubFile_ID] = 4, [LNK_CreatedBy_ID] = 4, [CreatedOn] = '20111230' WHERE [ExpanseId] = 2

IF NOT EXISTS (SELECT [ExpanseId] FROM [dbo].[Expanse] WHERE [ExpanseId] = 3)

INSERT INTO [dbo].[Expanse] ([ExpanseId], [ExpanseDate], [Amount], [LNK_File_ID], [LNK_SubFile_ID], [LNK_CreatedBy_ID], [CreatedOn])

VALUES (3, '20111230', 111, 2, NULL, 4, '20111230')

ELSE

UPDATE [dbo].[Expanse] SET [ExpanseDate] = '20111230', [Amount] = 111, [LNK_File_ID] = 2, [LNK_SubFile_ID] = NULL, [LNK_CreatedBy_ID] = 4, [CreatedOn] = '20111230' WHERE [ExpanseId] = 3

IF NOT EXISTS (SELECT [ExpanseId] FROM [dbo].[Expanse] WHERE [ExpanseId] = 6)

INSERT INTO [dbo].[Expanse] ([ExpanseId], [ExpanseDate], [Amount], [LNK_File_ID], [LNK_SubFile_ID], [LNK_CreatedBy_ID], [CreatedOn])

VALUES (6, '20111229', 110, 1, NULL, 4, '20111229')

ELSE

UPDATE [dbo].[Expanse] SET [ExpanseDate] = '20111229', [Amount] = 110, [LNK_File_ID] = 1, [LNK_SubFile_ID] = NULL, [LNK_CreatedBy_ID] = 4, [CreatedOn] = '20111229' WHERE [ExpanseId] = 6

IF NOT EXISTS (SELECT [ExpanseId] FROM [dbo].[Expanse] WHERE [ExpanseId] = 7)

INSERT INTO [dbo].[Expanse] ([ExpanseId], [ExpanseDate], [Amount], [LNK_File_ID], [LNK_SubFile_ID], [LNK_CreatedBy_ID], [CreatedOn])

VALUES (7, '20111229', 15, 1, 4, 4, '20111229')

ELSE

UPDATE [dbo].[Expanse] SET [ExpanseDate] = '20111229', [Amount] = 15, [LNK_File_ID] = 1, [LNK_SubFile_ID] = 4, [LNK_CreatedBy_ID] = 4, [CreatedOn] = '20111229' WHERE [ExpanseId] = 7

IF NOT EXISTS (SELECT [ExpanseId] FROM [dbo].[Expanse] WHERE [ExpanseId] = 8)

INSERT INTO [dbo].[Expanse] ([ExpanseId], [ExpanseDate], [Amount], [LNK_File_ID], [LNK_SubFile_ID], [LNK_CreatedBy_ID], [CreatedOn])

VALUES (8, '20111228', 19, 1, 5, 4, '20111228')

ELSE

UPDATE [dbo].[Expanse] SET [ExpanseDate] = '20111228', [Amount] = 19, [LNK_File_ID] = 1, [LNK_SubFile_ID] = 5, [LNK_CreatedBy_ID] = 4, [CreatedOn] = '20111228' WHERE [ExpanseId] = 8

IF NOT EXISTS (SELECT [ExpanseId] FROM [dbo].[Expanse] WHERE [ExpanseId] = 9)

INSERT INTO [dbo].[Expanse] ([ExpanseId], [ExpanseDate], [Amount], [LNK_File_ID], [LNK_SubFile_ID], [LNK_CreatedBy_ID], [CreatedOn])

VALUES (9, '20111230', 15, 3, 6, 4, '20111230')

ELSE

UPDATE [dbo].[Expanse] SET [ExpanseDate] = '20111230', [Amount] = 15, [LNK_File_ID] = 3, [LNK_SubFile_ID] = 6, [LNK_CreatedBy_ID] = 4, [CreatedOn] = '20111230' WHERE [ExpanseId] = 9

SET IDENTITY_INSERT [dbo].[Expanse] OFF

now my Stored Procedure

USE [AFMS]

GO

/****** Object: StoredProcedure [dbo].[SelectAll_ExpenseDetails] Script Date: 01/03/2012 18:06:40 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:Created By Archana

-- Create date: 09-12-2011

-- Description:<Description,,>

-- =============================================

ALTER PROCEDURE [dbo].[SelectAll_ExpenseDetails]

@LNK_File_ID numeric(18, 0) = NULL,

--@LNK_SubFile_ID numeric(18, 0) = NULL,

@LNK_Client_ID numeric(18, 0) = NULL,

@ExpanseDate datetime = NULL

-- varchar(max)

AS

BEGIN

SET NOCOUNT ON;

SELECT E.[ExpanseId]

,CONVERT(datetime,E.[ExpanseDate],103) as [ExpanseDate]

,E.[Amount]

,E.[LNK_File_ID]

,E.[LNK_SubFile_ID]

,CONVERT(datetime,E.[LNK_CreatedBy_ID],103) as [LNK_CreatedBy_ID]

,E.[CreatedOn]

,c.Client_FirstName as ClientName

,f.FileNo

,f.FileName

--,sf.SubFileNo

--,sf.SubFileName

FROM [AFMS].[dbo].[Expanse] E

JOIN [AFMS].[dbo].[Client] c on c.ClientId = E.[LNK_CreatedBy_ID]

JOIN [AFMS].[dbo].[File] f on f.FileId = E.[LNK_File_ID]

JOIN [AFMS].[dbo].[SubFile] sf on sf.SubFileId = E.[LNK_SubFile_ID]

Where

E.LNK_File_ID = Case when @LNK_File_ID IS NOT NULL THEN @LNK_File_ID

Else E.LNK_File_ID End and

--E.LNK_SubFile_ID = Case when @LNK_SubFile_ID IS NOT NULL THEN @LNK_SubFile_ID

--Else E.LNK_SubFile_ID End and

E.[LNK_CreatedBy_ID]= Case when @LNK_Client_ID IS NOT NULL THEN @LNK_Client_ID

Else E.[LNK_CreatedBy_ID] End and

E.[ExpanseDate]= Case when @ExpanseDate IS NOT NULL THEN @ExpanseDate

Else E.[ExpanseDate] End

End

exec [SelectAll_ExpenseDetails]

@LNK_File_ID = null,

@LNK_Client_ID = null,

@ExpanseDate = '29/12/2011 12:00:00 AM'

I am Executing this Stored Procedure

exec [SelectAll_ExpenseDetails]

@LNK_File_ID = null,

@LNK_Client_ID = null,

@ExpanseDate = '29/12/2011 12:00:00 AM'

but I am getting Error

Msg 8114, Level 16, State 5, Procedure SelectAll_ExpenseDetails, Line 0

Error converting data type varchar to datetime.

Archana Mistry

Sr. Programmer

Kintudesigns.com

1. Failure #1: Not understanding data type precedence
2. Failure #2: Not taking performance into consideration
3. Failure #3: Assuming all numbers are created equal
4. Failure #4: Relying on the ISNUMERIC built-in function
5. Failure #5: Underestimating the world of silent truncation
6. Failure #6: Not understanding date/time data
7. Failure #7: Importing Excel data without thought to data types
8. Failure #8: Treating XML just like any other string
9. Failure #9: Failing to take portability into account

Failure #1: Not understanding data type precedence

When a T-SQL expression attempts to combine data values of different types, the database engine applies the rules of data type precedence to determine how values should be implicitly converted. If the values cannot be converted, the database engine returns an error.

Data type precedence can play a significant role in a variety of operations. If you don’t understand how precedence works, you can end up with errors in places you least expect them-usually at times you can least afford them.

But understanding the rules means more than just knowing that DATETIME takes precedence over DECIMAL, and DECIMAL takes precedence over INT, and INT takes precedence over CHAR. Consider the following CASE expression:

In this example, the variable value equals def, so the first condition in the CASE expression evaluates to true and the SELECT statement returns the value 0. But what happens when we assign the value abc to the variable?

The SELECT statement now returns the following error:

The database engine is trying to convert the value does not apply to the INT data type, and that, of course, doesn’t work. A CASE expression returns the type with the highest precedence from the result expressions (the expressions after THEN and ELSE). In this instance, those values include two integers and one string (0, 1, and does not apply). That means the returned result expression must be an integer or be convertible to an integer. It doesn’t matter that the two conditions in the CASE expression evaluate to false. All the database engine cares about is that an integer takes precedence over a character data type.

One way to address this issue is to treat all the result expressions as strings:

Now the CASE expression returns the value does not apply. By specifying the result expressions as strings, we’ve put them on equal footing in the eyes of the precedence gods.

For our example here, enclosing the values in single quotes did the trick, but in many situations, you’ll likely have to use CAST or CONVERT to explicitly convert the values, such as when you pass in variables or columns. The point is, you have to understand how data is converted and what that means to your expressions. Otherwise, you can wind up with a nightly batch process that fails every third run for no apparent reason.

Such issues are hardly limited to CASE expressions. Let’s take a look at the COALESCE function in action:

We declare a set of variables and then use COALESCE to return the first value that does not evaluate to NULL. As expected, the SELECT statement returns 1234. Now let’s switch the variable order:

This time, the statement generates an error, even though the @a variable contains a non-NULL value:

Once again, the database engine is trying to convert data and the conversion is failing. Similar to a CASE expression, COALESCE returns the data type of the expression with the highest precedence. The @d variable is defined as an INT, which has precedence of the other variable types. As a result, any value returned by the function, other than NULL, must be an integer or convertible to one.

The way around this is, of course, to use CONVERT or CASE to explicitly convert the integer to a character data type:

Now the SELECT statement will return a value of abc. That said, it’s not enough to simply convert numeric data to a string. Take a look at what happens when we mix things up again:

The SQL_VARIANT_PROPERTY function lets us view details about the value being returned. As the following results show, the @a value is returned as VARCHAR(10):

FirstValue	ValueType	TypeLength
abc	varchar	10

Although the VARCHAR data type adds only a couple bytes per value, those bytes can add up to a significant amount if we’re talking billions of rows, especially if those rows are sitting in memory.

The ISNULL function is another one that can cause unexpected issues. The function replaces the first value with the second value if the first value is NULL, as shown in the following example:

Because the @d variable is NULL, the SELECT statement returns a value of 1234. Now suppose we specify @a as the second value:

Once again, the database engine generates a conversion error:

Unless you pass in a literal NULL as the first expression, ISNULL uses that expression’s type for the returned value. In this case, the type is INT, which means the database engine is trying to convert abc to a numeric type. Not only can this lead to an error, as we received here, but it can also lead to an odd sort of truncation:

The first value this time around is of the type CHAR(2). When we try to convert the @c value to CHAR, we don’t get an error, but rather an asterisk:

FirstValue	ValueType	TypeLength
*	char	2

The problem is not one of a failed implicit conversion, but rather one of trying to turn an INT value into a CHAR(2) value. If it were CHAR(4), the conversion would be fine. Instead, we end up in conversion limbo, a likely carryover from the early days of handling database overflow errors, before error handling got a more reputable foothold. Imagine trying to insert asterisks into those data warehouse columns configured with the INT data type.

You should learn how data type precedence works and how it is applied before these types of problems arise. A good place to start is with the MSDN topic “Data Type Precedence.” But don’t stop there. You should also know how your expressions, functions, and other elements treat data when it is implicitly converted. Your best strategy is to explicitly convert the data when you know a conversion is imminent and to provide the logic necessary to handle different possible scenarios. Otherwise, you leave yourself open to chance, which seldom works as a long-term strategy.

Failure #2: Not taking performance into consideration

Not only can implicit conversions wreak havoc by generating unexpected errors (or those pseudo-error asterisks), but they also take their toll on performance. Let’s look at an example of a basic SELECT statement that retrieves data from the AdventureWorks2014 sample database:

The statement includes a WHERE clause that specifies a NationalIDNumber value, which is stored in the Employee table as NVARCHAR(15) data. Because we’re capturing the I/O statistics, we receive the following information as part of our results:

There are two important statistics worth noting here. The first is that an index scan is being performed, rather than a seek, and the second is that it takes six logical reads to retrieve the data. If we generate an execution plan when we run the query, we can view information about the scan by hovering over the scan icon in the execution plan. The following figures shows the details about the scan:

First, take a look at the Predicate section. The database engine is using the CONVERT_IMPLICIT function to convert the NationalIDNumber value in order to compare it to the 948320468 value. That’s because we’re passing the value in as an integer, so the database engine must implicitly convert the column value to an INT to do the comparison.

Now let’s rerun the statement, only pass the NationalIDNumber value in as a string:

This time, our statistics show that the database engine performs no scans and only four logical reads:

If we generate the execution plan, we can view details about the seek, which shows a conversion, but only in terms of data length, with no scan performed. We even get better statistics in operator, I/O, and CPU costs.

This, of course, is only one query retrieving one row based on one value. But start multiplying those values and rows and queries and you can end up with monstrous performance hits because you’re not paying attention to how your data is being converted.

Failure #3: Assuming all numbers are created equal

Numerical data likes to play tricks on us, especially when implicit conversions are involved. If we’re not careful, we can end up with results we don’t expect or want, often without any hint that there’s a problem.

Take, for example, the following T-SQL, which converts decimal data to integer data:

You might expect SQL Server to handle this gracefully and round the 345.56 to 346. It does not. Instead, the SELECT statement returns a value of 345. The database engine simply truncates the value, without any attempt at rounding.

What appears as only a slight loss here can translate to big losses to the bottom line. Suppose the original decimal value refers to shipping weights. If customers should be charged based on the next highest whole number, but your database is always truncating the value, someone is going to have to eat the costs for all the weight that’s not been accounted for.

There are ways to address such situations. For example, you might use the CEILING function to round the value up:

Now the SELECT statement returns a value of 346, an amount sure to keep the accounting department happy. However, other issues await. Let’s look at what happens when we try to add a decimal and integer together:

Because of data type precedence, SQL Server converts the integer to a decimal and then adds the two together. Although the database engine handles the conversion without a hiccup, it does increase the precision:

Total	ValueType	TypePrecision	TypeScale
12690.67	decimal	13	2

The increased precision might not seem a big deal, but it can add up. According to SQL Server documentation, a decimal with a precision from 1 through 9 requires five bytes of storage. A decimal with a precision of 10 through 19 requires nine bytes of storage.

You need to understand how precision works whenever you’re converting numeric data. Not only do you risk extra overhead, but you could also end up with a less-than-happy database engine. Let’s recast the last example in order to insert the sum into a table variable:

When we try to insert the data, we receive the following error:

If variable @a had been a smaller number, such as 123, we would have received no error. The same is true if we change the precision of ColA to match to match the sum, in which case the insert will run with no problem:

As the following results show, the ColA value is now configured as DECIMAL(7,2):

ColA	ColType	TypePrecision	TypeScale
12690.67	decimal	7	2

The point of all this is that you must be prepared to handle whatever type of numeric data comes your way, which means you need to understand how the numeric data types work in SQL Server, especially when you start converting data.

Let’s look at another example of what might go wrong. In the following T-SQL, we compare REAL and INT values:

In this case, we’re implicitly converting the @b integer to a REAL value and then comparing that value to the @a integer, using a CASE expression to test for equality. Based on the original values of the two integers, we might expect the CASE expression to return values not equal. Instead, we get the following results:

CheckEquality	VarCValue
values equal	100000000.0000000000000000

The STR function let’s us easily view the actual value being stored in the @c variable, rather than scientific notation. As you can see, there is no hint of the 1 that was there before we converted the data. The problem is that the REAL data type, like the FLOAT data type, is considered an approximate-number data type, which means not all values in the permitted range can be represented exactly. If you plan to compare or convert REAL or FLOAT data, you better understand the limitations of those types. Otherwise that rocket you’re sending to the next passing asteroid might end up boldly going where no one has gone before

Also be aware of how SQL Server handles numeric data when used in conjunction with non-numeric data, particularly when trying to add or concatenate values. For example, if you try to add two values in which one is an integer and one is a string, the database engine implicitly converts the string type to the numeric type and adds the values together:

As the following results show, the two values are added together and an integer is returned:

EndValue	BaseType	TypeLength
579	int	4

The database engine converts the character data to an integer because the INT data type takes precedence over the CHAR data type. If what you’re actually after is to concatenate the two values, then you must explicitly convert the integer to a string:

Now the results show the concatenated value and the CHAR data type:

EndValue	BaseType	TypeLength
123456	char	6

Differentiating between adding values and concatenating values, like any aspect of numerical data, requires that you understand how numeric data types work, how data type precedence works, and how T-SQL elements work with numeric data. Otherwise, you can never be sure you’re getting the results you had actually expected.

Failure #4: Relying on the ISNUMERIC built-in function

One T-SQL element in particular that can catch developers off-guard is the ISNUMERIC function. The function tests an expression to determine whether it produces a numeric type. If it does, the function returns the value 1; otherwise, it returns a 0. The challenge with this function is that it can sometimes interpret a value as numeric even if it contains no numbers.

Let’s look at an example. The following T-SQL creates a table variable, adds an assortment of string values to the variable, and then uses a CASE expression to test whether those values are considered numeric:

If a value is numeric, the SELECT statement tries to convert the value to the INT data type; otherwise, the statement returns a NULL. Unfortunately, when the CASE expression bumps up against the value $456, the database engine generates the following error:

The ISNUMERIC function is actually quite liberal when deciding what constitutes a numeric value. In this case, it sees a dollar sign and interprets the value as numeric, yet when the CASE expression tries to convert the value to an integer, the database engine baulks.

To get a better sense of what the function considers to be numeric, let’s recast our SELECT statement:

As the following results show, the ISNUMERIC function interprets all values except abc as numeric:

ColA	TestResults
abc	0
123	1
$456	1
7e9	1
,	1
$.,	1

For the value $456 it’s easy to see how the function can interpret this as money and consequently a numeric type. The next value, 7e9, also makes sense because the function sees it as scientific notation. What is not so clear is why the last two values are considered numeric. If such values are possible in your data set, relying on the ISNUMERIC function to control your statement’s logic when converting data can lead to an assortment of problems.

If you’re running SQL Server 2012 or later, you can instead use the TRY_CONVERT function to test your values before converting them:

If the value cannot be converted to an integer, the CASE expression returns the value 0; otherwise, it returns a 1, as shown in the following results:

ColA	TestResults
abc	0
123	1
$456	0
7e9	0
,	0
$.,	0

This time we have a more reliable assessment of the data. Unfortunately, if you’re running a version of SQL Server prior to 2012, you’ll have to come up with another way to check for numeric values before trying to convert them. Just be careful not to rely on the ISNUMERIC function alone unless you’re certain about the predictability of the data you’ll be converting.

Failure #5: Underestimating the world of silent truncation

If a value will be truncated when inserting it into a column, the database engine returns an error, warning you of the possible truncation. Unfortunately, the database engine is not so diligent in all cases, particularly when it comes to variables and parameters. One wrong move and you can end up with a database full of truncated data and a recovery scenario that leaves you without sleep for the next three months.

Let’s look at a simple example of what can happen:

When we attempt to set the value of @b to @a, the database engine happily obliges, as evidenced by the SELECT statement’s results:

VarValue	ValueType	TypeLength
abc	char	3

The original value, abcdef, has been seamlessly truncated to conform to the CHAR(3) type. The same thing can happen if we run an ISNULL function against the values:

The statement returns the same results as the preceding SELECT, with the original value truncated. Unless we pass in a literal NULL as the first expression, ISNULL uses the type of the first expression, which in this case is CHAR(3).

Parameters too can fall victim to the sinister world of silent truncation:

The target column in the #a temporary table is configured as CHAR(5) and the stored procedure’s parameter @a as VARCHAR(5), which would suggest no room from truncation. However, unless you explicitly check the parameter’s input value, you could run into problems. For example, suppose we pass in a value larger that what the parameter supports when calling the procedure:

The database engine will silently truncate the value and insert a shortened version, giving us a returned value of ab cd, all without any sort of error.

Another issue to watch for is if ANSI warnings are turned off during your insert operations. By default, the warnings are turned on, which is why the database engine generates an error if a value will be truncated when inserting it into a column:

By default, the statement generates the following error:

However, it’s not uncommon to set ANSI warnings to off in certain cases, such as bulk load operations:

This time, the database engine does not return an error. It simply truncates the data and sticks what’s left into the table, giving us the value abc.

You must be vigilant against silent truncations when data is being converted from one type to another, even if it’s only a smaller size of the same type. Truncations can and do occur without anyone realizing what has happened-until it’s too late.

Failure #6: Not understanding date/time data

Date/time values in SQL Server can be full of surprises when converting data, often because of the format used to pass in the date. For instance, the following T-SQL tries to convert a date that follows the day-month-year format:

When the database engine tries to convert the string, it returns an out-of-range error:

In this case, the SQL Server instance is configured to use US English, but the date conforms to British and French standards. Suppose we recast the date as follows:

The SELECT statement now returns the following results:

SQL Server follows specific date/time conventions based on the configured language. Look at what happens when we change the language:

The database engine converts the date with no problem, and our SELECT statement returns the value we expect. Now let’s run the T-SQL again, only change the language to US English:

This time around, we receive an out-or-range conversion error because the date follows the day-month-year convention. If you’re not prepared for these differences, you can end up with out-of-range conversion errors all over the place.

One way to address this issue is to use the CONVERT function to specifically convert the string value to the necessary format:

The third argument in the CONVERT function, 103, specifies that the value to be converted should be in the British/French style. As a result, the SELECT statement will now return our date/time value as expected.

Another approach is to set the DATEFORMAT property. Normally, the selected language determines the property’s value, but we can override it:

We’ve set the DATEFORMAT property to dmy for day-month-year. The British/French version of the date can now be converted with no problem, and if we want to return to the US English format, we can change the property setting once again:

Using the CONVERT function or setting the DATEFORMAT property are fine approaches if we know the source language. If we have no way to determine the source of the data within the database, it makes this process more difficult, and we have to rely on the application to tell us the language or to enforce a particular format.

If you can determine the language based on the connection to the data source, you can change your settings to target that language before importing the data, and then change the language back when the import is complete. Robyn Page offers great insight into this and other issues related to date/time values and languages in her article “Robyn Page’s SQL Server DATE/TIME Workbench.”

Also be aware that you can run into issues when converting data from one date/time type to another. Suppose you’re trying to convert DATETIME data to SMALLDATETIME:

Notice the year: 2099. This works fine for the DATETIME data type, but not SMALLDATETIME. When we try to convert the data, the database engine generates the following out-or-range conversion error:

The DATETIME data type supports the years 1753 through 9999, but SMALLDATETIME goes only from 1900 through 2079. Perhaps for many of your needs, the smaller range is enough. But all it takes is one out-of-range year to bring your bulk load operation to a halt.

Even when you can convert a date from DATETIME to SMALLDATETIME, you should also take into account the loss of precision in seconds:

The following table shows the results returned by the SELECT statement:

VarA	VarB
2015-03-18 11:33:10.560	2015-03-18 11:33:00

Notice that we lose over 10 seconds when converting from DATETIME to SMALLDATETIME. The database engine essentially truncates the value at the minute mark and returns only zeroes for the seconds. A few seconds here or there might not seem much, but they can add up and impact analytical processes that require precise calculations down to the hundredth of a second.

Whenever you’re working with date/time values, you have to take their nature into account. Consider the following example:

The SELECT statement returns the following results (formatted as a list for easy viewing):

When converting data to or from a date/time data type, you should have a good sense of how the data types work and conversions work and what can happen to your values-before you start tracking data for the next Olympic trials.

Failure #7: Importing Excel data without thought to data types

Importing Excel data can be tricky unless you know exactly what types of data the spreadsheet contains and you’re certain the data types will always be the same. But if you’re importing data from an Excel spreadsheet on a regular basis, and that spreadsheet is continuously updated, you run the risk of data types changing, which can making converting the data unpredictable, unless you’re prepared for the possible changes.

Here’s the problem. When you import Excel data into SQL Server, the OLE DB provider guesses at a column’s data type by sampling a set of rows and going with the majority. By default, that sample is made up of the spreadsheet’s first eight rows. For example, if a column contains five numeric values in the first eight rows, the provider assigns the FLOAT data type to that column (even if all numbers are integers). When this occurs, any non-numeric values are returned as NULL.

Although, there are ways to work around this limitation, such as adding the IMEX property to the provider string and setting its value to 1, the issue points to another challenge. Under certain circumstances, the provider can return different data types for the same column, depending on how the data has been updated between import operations. If you’re T-SQL statements convert the data as it’s coming into the database, you need to include the logic necessary to handle the possibility of changing types.

For instance, suppose you use a SELECT...INTO statement and the OPENROWSET function to retrieve data from an Excel spreadsheet and load it into a temporary table. You also define the provider string to take into account the possibility of a column containing mixed types. As to be expected, the provider will determine the table’s column types based on the sampled values in the spreadsheet.

One of the columns is supposed to contain only integers, but occasionally alphanumeric values find there way into the column. As a result, the provider will create the corresponding column in the temporary table as either FLOAT or VARCHAR(255), depending on the balance in the sample. Let’s look at temporary table initially set up with a single VARCHAR column:

Suppose that ColA is usually configured with the INT data type, but in this case, it was assigned the VARCHAR data type to accommodate those wayward string values. If any T-SQL code contains logic relying on the column being INT, our statements could fail, which might be particularly confusing if we don’t understand how the OLE DB provider determines data types.

To safeguard against this issue, we need to incorporate the logic necessary to handle the possibility of changing types:

In this case, we’re saying that, if the data isn’t right, set the value to 0 until we can fix it; otherwise, convert the value to an integer, giving us the following results:

ColA	CheckA
101	101
102ex	0
103	103
1o4	0

You might take a different approach, of course, or many different approaches. What’s important here is that, unless you can rely on your Excel spreadsheets to always provide the same types of data, you have to be prepared for the possibility of unexpected changes.

Failure #8: Treating XML just like any other string

Converting string data to XML is relatively painless as long as the string is well formed XML, in which case you can do something similar to the following to carry out your conversion:

All we’re doing here is explicitly converting variable @a and assigning it to variable @b, which has been configured with the XML data type. The SELECT statement gives us the following results:

One consequence of converting the data in this way is that we lose the tabs and linefeeds that help make the XML more readable. In many cases, the way in which the XML is displayed will not be an issue. However, if you want to preserve those tabs and linefeeds, you need to add the style argument to the CONVERT function:

The style argument (1) tells the database engine to preserve any insignificant white space, such as tabs and linefeeds. Now the SELECT statement returns results that look like the following:

Given how effectively the CONVERT function handles this conversion, we might expect it to work the same way when we’re converting XML data to VARCHAR data:

Unfortunately, adding the style argument doesn’t help and we get the following results:

The problem has to do with variable @a. The database engine is implicitly converting the string value to the XML type, which means that the tabs and linefeeds are not being preserved during that assignment. The way to get around this is to use the CONVERT function to explicitly cast the string to the XML type when assigning the value to the data type:

Now our results are more in line with what we want:

None of this is in itself a big deal, but let’s look at what happens if we convert the XML to the VARCHAR type without specifying the style argument when setting the value of @b:

Suddenly our results look quite different:

SQL Server’s XML parser attempts to store special characters such as tabs and linefeeds in a way that preserves them throughout various processing operations, including retrieving and converting data. This process, known as entitization, saves tabs as and linefeeds as , but tabs and linefeeds are only two types of special characters that the parser can entitize.

In most cases, when you retrieve XML directly, these special characters are automatically displayed in a readable format. That said, the XML parser is not always consistent in how it treats entitized characters. Notice in the preceding results that only one linefeed is returned as . In addition, if the database engine comes across a special character it doesn’t like, it returns an error, rather than trying to entitize it. For instance, the following example includes an ampersand (&) in each XML element:

The code now generates the following error message:

When possible, you can manually entitize the special characters:

But even in this case, the CONVERT function’s style argument doesn’t return the actual ampersand, only the entitized code, giving us the following results:

If we want to see an actual ampersand, we need to replace the entitized character:

Now our results are closer to what we expect:

If you’re converting XML data, you have to take into account how the XML parser works in SQL Server. Otherwise, you can run into problems in unexpected ways. Even an entitized character can be a showstopper.

Failure #9: Failing to take portability into account

Some organizations have been working with SQL Server since its humble beginnings and plan to continue to do so, having no intention now or in the future to port their databases to another system. As long as SQL Server is out there, that’s where they plan to stay.

For other organizations, the future is not quite so clear-cut. Under the right circumstances, they’d be more than willing to port their databases to another system, providing the penalty for doing so isn’t too high.

If there is any chance you’ll be among those who will one day jump ship, you need to take into consideration portability when you write your T-SQL code, and an important part of that consideration is how data is being converted.

A good place to start is to quit relying on implicit conversions, such as the one shown in the following T-SQL:

The example converts a DATETIME value to VARCHAR, as shown in the following results:

VarValue	ValueType	TypeLength
Mar 13 2015 11:16AM	varchar	25

Although SQL Server has no problem converting the data and returning the results we expect, we cannot be sure another database system will handle the conversion so easily or in the same way. The solution, of course, is to make all our conversions explicit:

The SELECT statement again returns the results we expect, but helps to avoid performance issues and surprises. However, this leads to another concern. The CONVERT function is specific to SQL Server. If we want to make our code portable, we need to go with the CAST function:

The CAST function works just like the CONVERT function in this case, except that CAST conforms to ISO specifications. Any database system that adheres to these standards will be able to handle the conversion without the code needing to be modified. With CAST, we lose the style features available to CONVERT, but we’re making the code more portable and avoiding implicit conversions.