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
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[.
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.
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.
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())
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With