Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Problem with SQL Server smalldatetime insert query

I have the field:

APP_DATE (smalldatetime)

I'm doing this query:

INSERT INTO table (TYPE, CODE, APP_DATE, DATE) VALUES ('APP', '123', '02/10/2010 12.30', GETDATE())

It fails:

Msg 296, Level 16, State 3, Line 1
Conversion from datatype char to smalldatetime generated a value not between the interval of valid values.
Instruction has been interrupted.

(0 row(s) affected)

What am I doing wrong? It appears to me as the correct format for the field..

Thank you for your time.

EDIT: SQL Server 2000

like image 511
0plus1 Avatar asked Feb 23 '10 13:02

0plus1


People also ask

How do I write Smalldatetime in SQL?

smalldatetime – format is YYYY-MM-DD hh:mm:ss; stores values from 1900-01-01 to 2079-06-06; with the accuracy of 1 minute; uses 4 bytes. datetime2 –format is YYYY-MM-DD hh:mm:ss[.

What does Smalldatetime mean in SQL?

SQL DateTime. The SMALLDATETIME data type specifies a date and time of day in SQL Server. SMALLDATETIME supports dates from 1900-01-01 through 2079-06-06. The default value is 1900-01-01 00:00:00. The seconds are always set to 0, and fractional seconds are not included.

How can I improve my insert query performance?

To optimize insert speed, combine many small operations into a single large operation. Ideally, you make a single connection, send the data for many new rows at once, and delay all index updates and consistency checking until the very end.


1 Answers

Can you try to use the ISO-8601 date format (YYYYMMDD HH:MM:SS) - this will work always on SQL Server - regardless of your regional and locale settings:

INSERT INTO table (TYPE, CODE, APP_DATE, DATE) 
VALUES ('APP', '123', '20100210 12:30:00', GETDATE())
like image 50
marc_s Avatar answered Sep 22 '22 04:09

marc_s