Prev: missing Index DMVs
Next: BULK INSERT with Format File..
From: AussieRules on 26 Mar 2010 07:48
Hi,

I have a vb.net application that is inserting data into a sql table. one of
the cols in my table, is of type date.

My sql statement is

insert into tbl_opsJournal
(article,action,quantity,container,status,job_number,date)
values ('R5W','-',6,'00101','00','123','26/3/2010 13:41:00')


The insert fails with
The conversion of a carchar data type to a datetime data type resulted in an
out-of-range value...


If I change the date to be 3/26/2010.... then it works...

Looking at the control panel, the regional options is set to be Hong Kong
(SAR), and the short date format example is 26/3/2010, so it looks like the
format is correct.

I am leaving Hong Kong tomorrow, and need to have this solved... is there
another area where I have to set the date format in SQL 2008 ?

Thanks for the help...


From: Tibor Karaszi on 26 Mar 2010 08:09
Regional setting is irrelevant for datetime input. Here's the full story:

http://www.karaszi.com/SQLServer/info_datetime.asp

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi



"AussieRules" <nospam(a)nospam.com> wrote in message
news:#covDpNzKHA.244(a)TK2MSFTNGP06.phx.gbl...
> Hi,
>
> I have a vb.net application that is inserting data into a sql table. one
> of the cols in my table, is of type date.
>
> My sql statement is
>
> insert into tbl_opsJournal
> (article,action,quantity,container,status,job_number,date)
> values ('R5W','-',6,'00101','00','123','26/3/2010 13:41:00')
>
>
> The insert fails with
> The conversion of a carchar data type to a datetime data type resulted in
> an out-of-range value...
>
>
> If I change the date to be 3/26/2010.... then it works...
>
> Looking at the control panel, the regional options is set to be Hong Kong
> (SAR), and the short date format example is 26/3/2010, so it looks like
> the format is correct.
>
> I am leaving Hong Kong tomorrow, and need to have this solved... is there
> another area where I have to set the date format in SQL 2008 ?
>
> Thanks for the help...
>
>
 | 
Pages: 1
Prev: missing Index DMVs
Next: BULK INSERT with Format File..