Sql ошибка при преобразовании типа данных varchar к datetime

set dateformat mdy

select CAST('03/28/2011 18:03:40' AS DATETIME)

set dateformat dmy

select CAST('03/28/2011 18:03:40' AS DATETIME)

12

4

It’s a date format issue. In Ireland the standard date format for the 28th of March would be «28-03-2011», whereas «03/28/2011» is the standard for the USA (among many others).

I know that this solution is a little different from the OP’s case, but as you may have been redirected here from searching on google the title of this question, as I did, maybe you’re facing the same problem I had.

Sometimes you get this error because your date time is not valid, i.e. your date (in string format) points to a day which exceeds the number of days of that month!
e.g.: CONVERT(Datetime, '2015-06-31') caused me this error, while I was converting a statement from MySql (which didn’t argue! and makes the error really harder to catch) to SQL Server.

1

JAVA8: Use LocalDateTime.now().toString()

i faced this issue where i was using SQL it is different from MYSQL
the solution was puting in this format:
=date(‘m-d-y h:m:s’);
rather than
=date(‘y-m-d h:m:s’);

set dateformat mdy

select CAST('03/28/2011 18:03:40' AS DATETIME)

set dateformat dmy

select CAST('03/28/2011 18:03:40' AS DATETIME)

12

4

It’s a date format issue. In Ireland the standard date format for the 28th of March would be «28-03-2011», whereas «03/28/2011» is the standard for the USA (among many others).

I know that this solution is a little different from the OP’s case, but as you may have been redirected here from searching on google the title of this question, as I did, maybe you’re facing the same problem I had.

Sometimes you get this error because your date time is not valid, i.e. your date (in string format) points to a day which exceeds the number of days of that month!
e.g.: CONVERT(Datetime, '2015-06-31') caused me this error, while I was converting a statement from MySql (which didn’t argue! and makes the error really harder to catch) to SQL Server.

1

JAVA8: Use LocalDateTime.now().toString()

i faced this issue where i was using SQL it is different from MYSQL
the solution was puting in this format:
=date(‘m-d-y h:m:s’);
rather than
=date(‘y-m-d h:m:s’);

Answers

• Wow, so many answers on such a basic question, and no one can give the correct answer!

  What is going on is that the optimizer may decide evaluate expressions earlier for better performance, but at the risk that things go wrong. I don’t like it, but Microsoft don’t seem to give in. And similar issues exists in other engines.

  Anyway, the only guarantee to avoid that this does not happen is to use CASE:

  UPDATE SalesTempTest
  set    out_date = CASE WHEN isdate(out_date) = 1
                         THEN Convert(nvarchar(20),
  
                               Cast(out_date as datetime),101)
                    END
  Where ISDATE(out_date) = 1;

  Of course, using (n)varchar for datetime values is a bad idea, but if you get data on flat files, bad dates are part of the game.

  ---

  Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

  • Marked as answer by
    Mister T99
    Saturday, April 6, 2013 1:56 PM

Ответы

• You are doing

  cast(convert(datetime, right([start],11), 131)as time)

  which gets the last 11 characters and tries to convert the value to a datetime.  You need the characters beginning with the 11th character which would be

  cast(convert(datetime, substring([start],11,len([start])), 131)as time)

  Tom

  • Помечено в качестве ответа

    18 июля 2019 г. 4:15

• Then you will need to put those years of tsql experience to work to write your own conversion and validation logic. If you want help, you need to explain what range of values exist and the logic you propose to convert.

  You should know by now that CASE is an expression that returns a single value of a specific datatype. Your first branch returns an empty string but your second branch returns a datetime. That empty string will be implicitly converted to datetime resulting
  in a value that you probably do not want to see. In addition, you can convert the single value you included in your sample data directly to datetime without the need for your logic (using standard US regional settings). 

  I put some sample data into
  dbfiddle to demonstrate converting in different ways — and to demonstrate some of the problems with your logic. As already mentioned your logic to isolate the time was incorrect. Your logic to isolate the date was also overly complicated — but at least
  did not produce an error. Of course, all this assumes you have valid dates and times stored in a consistent pattern. You need to verify that assumption. 

  • Помечено в качестве ответа
    lrj1985
    18 июля 2019 г. 4:15

• Hi lrj1985,

  scott_morris-ga already provided a full answer above.

  Just in case, if you like string manipulations, please try the following:

  DECLARE @tbl TABLE
  (
      ID INT IDENTITY(1, 1) PRIMARY KEY,
      Start VARCHAR(30)
  );
  
  INSERT INTO @tbl(Start)
  VALUES
  ('07/17/2019 6:00 PM');
  
  -- Date and time format - ISO 8601
  SELECT *
  	, CAST(SUBSTRING([Start],7, 4) + '-' 
  		+ SUBSTRING([Start], 1, 2) + '-'
  		+ SUBSTRING([Start], 4, 2) + 'T'
  		+ CAST(CAST(CONVERT(DATETIME, substring([Start], 11,len([start])), 131) AS TIME) AS CHAR(8))AS DATETIME) AS RealDateTime
  FROM @tbl;

  Output:

  ID	Start	                RealDateTime
  1	07/17/2019 6:00 PM	2019-07-17 18:00:00.000

  • Изменено
    Yitzhak Khabinsky
    18 июля 2019 г. 4:08
  • Помечено в качестве ответа
    lrj1985
    18 июля 2019 г. 4:15