insert time type data in sql server

Question

insert time type data in sql server

Navigation

#1 by (0 votes)

0

I'm making a data set for an application, and when I insert a Time record, it gives me the following error:

INSERT INTO [ope_prodqa_dev] 
   (
      staff, 
      turn, 
      date_prodqa,
      hour_dev
    ) 
   VALUES 
   (
      1,
      481,
      (SELECT CONVERT (datetime, '2016-10-23 20:44:11', 120)), 
      (SELECT CONVERT time,'12:05:06.0000000'))
    );

hour_dev is type TIME(7) that must be saved with this format 07:45:00.0000000 but when doing the INSERT I get it:

Error al convertir una cadena de caracteres en fecha y/u hora.

sql-server tsql time query

asked by miss Robot 19.06.2018 в 10:21

source

1 answer

Display project AngularCLI in sevidor NodeJs Error in iReports plugin with netbeans

score 0 · Answer 1

Since it is an insert, you can simply use CAST in this way and without having to incur any type of format management, letting the system enter the values with the best format:

    INSERT INTO ope_prodqa_dev  
    (
      staff, 
      turn,
      date_prodqa, 
      hour_dev  
    ) 
   VALUES 
   (
      1,
      481,
      CAST ('2016-10-23 20:44:11' AS DATETIME), 
      CAST ('12:05:06.0000000' AS TIME)
    );

The result would be:

    staff   turn        date_prodqa                 hour_dev
    ----------------------------------------------------------
    1       481         23.10.2016 20:44:11         12:05:06

If you are in SQL Server 2012+, you should use TRY_CAST instead of CAST :

    INSERT INTO ope_prodqa_dev  
    (
      staff, 
      turn,
      date_prodqa, 
      hour_dev  
    ) 
   VALUES 
   (
      1,
      481,
      TRY_CAST ('2016-10-23 20:44:11' AS DATETIME), 
      TRY_CAST ('12:05:06.0000000' AS TIME)
    );

The result would be the same:

    staff   turn        date_prodqa                 hour_dev
    ----------------------------------------------------------
    1       481         23.10.2016 20:44:11         12:05:06

The interesting thing is that TRY_CAST will prevent the query from failing if there is an incorrect value. Inserting NULL in that case.

Suppose this query, where, by mistake, you try to pass this date: XYZ2016-10-23 20:44:11

If you use only CAST , you would have this error:

Conversion failed when converting date and/or time from character string.

But if you use TRY_CAST the query will work, inserting NULL in the column.

    INSERT INTO ope_prodqa_dev  
    (
      staff, 
      turn,
      date_prodqa, 
      hour_dev  
    ) 
   VALUES 
   (
      1,
      481,
      TRY_CAST ('XYZ2016-10-23a 20:44:11' AS DATETIME), 
      TRY_CAST ('12:05:06.0000000' AS TIME)
    );

The result would be:

    staff   turn        date_prodqa                 hour_dev
    ----------------------------------------------------------
    1       481         NULL                        12:05:06

If the columns do not support null values, then you can combine it with ISNULL , so that you enter 01.01.1900 00:00:00 in the failed values.

For example:

    INSERT INTO ope_prodqa_dev  
    (
      staff, 
      turn,
      date_prodqa, 
      hour_dev  
    ) 
   VALUES 
   (
      1,
      481,
      ISNULL(TRY_CAST ('XYZ2016-10-23 20:44:11' AS DATETIME),''),
      ISNULL(TRY_CAST ('120:05:06.0000000' AS TIME),'')
    );

The result would be:

    staff   turn        date_prodqa                 hour_dev
    ----------------------------------------------------------
    1       481         01.01.1900 00:00:00         00:00:06

If you want to do it anyway with CONVERT :

INSERT INTO ope_prodqa_dev  
    (
      staff, 
      turn,
      date_prodqa, 
      hour_dev  
    ) 
VALUES 
   (
      1,
      481,
      CONVERT(DATETIME,'2016-10-23 20:44:11',120), 
      CONVERT (TIME,'12:05:06.0000000')
    );

If you are interested in null handling and you do not have SQL Server 2012+, you can adapt the code to correctly handle possible erroneous values. I do not insist too much on it because I do not know if it interests you, nor is it the main problem of the question.

On the other hand, if you use SQL Server 2012+ it would be more interesting to use TRY_CONVERT :

    INSERT INTO ope_prodqa_dev  
    (
      staff, 
      turn,
      date_prodqa, 
      hour_dev  
    ) 
   VALUES 
   (
      1,
      481,
      ISNULL(TRY_CONVERT(DATETIME,'2016-10-23 20:44:11',120),''), 
      ISNULL(TRY_CONVERT (TIME,'12:05:06.0000000'),'')
    );