Problem with SQL Server smalldatetime insert query

Question

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

Fechter

Answer 1

Just use : in your time instead of .. Then it will insert fine.

Antipus

Ah, I have SQL Server 2005. Maybe that's the problem. Try changing the format of the date, maybe '2010-19-02 16:28' ? Or perhaps you've got the wrong locale settings, does it think 19 is the month? – Antipus

Answer 2

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())

Lovelorn

Answer 3

The date time format isn't correct; the year should be 4 digit (otherwise it is ambiguous) and the time separator should be a colon.

2003/01/22 22:31 will work. see this article.

Amplifier

Answer 4

insert into yourTable(yourSmallDateTimeColumn) values('1900-01-01 11:45:23');//format "yyyy-MM-dd HH:mm:ss"

Mulderig

Answer 5

If you do not need the time element in the field SQL Server will default it to 00:00:00 for you if you just send in '02/10/2010' If you need the time element then your format for the time is wrong in your example and it should be something like this - '02/10/2010 12:31:00'

Farnese

Answer 6

at PHP you have function date, you can write the format you want inside like this example

$smallDateAndTime =date('Y-m-d H:m:s');

Vulcanism

Please explain your solution in a bit more detail. – Bartolome