insert date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

insert date

Author

Topic

imughal
Posting Yak Master

192 Posts

Posted - 2005-04-04 : 01:19:11

hi,

i m trying to insert data with date. i have datetime datafield. on insert i m getting following error.

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

when i try to insert 28/04/2005 [dd/mm/yyyy].

insert into task_master values ('fdsa','fdsafs',2,19, 1 ,'04/04/2005' , '28/04/2005','04/04/2005', NULL, 1,1,' dafsdsfas' ,'', 5,3, 'IT-2005040003',7)

but getting error pls tell me how to fix it.
thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-04-04 : 02:18:59

Try this

insert into task_master values ('fdsa','fdsafs',2,19, 1 ,
'04-Apr-2005' , '28-Apr-2005','04-Apr-2005', NULL, 1,1,' dafsdsfas' ,'', 5,3, 'IT-2005040003',7)

Madhivanan

Failing to plan is Planning to fail

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-04-04 : 02:29:46

Use ISO Dates: yyyymmdd, and You will not have this problem.
You can also specify: SET DATEFORMAT dmy

rockmoose

andy8979
Starting Member

36 Posts

Posted - 2005-04-04 : 10:05:40

Test the following settings I found it on another forum I haven't tried

This is due to an inconsistency in the language settings between the web server and SQL server (or SQL server user). By default, SQL Server users are set to "us_english" as the default language. The result is that different date formats are being used and are therefore not recognized by the database.

There are several options to remedy this situation:

1. Change the SQL user's default language to match the language settings on the SQL server.
To do this, open SQL enterprise manager and connect to your database server. Expand Security -> Logins and open the properties dialog for the user that you are using to connect to the database. On the main properties page, change the language to equal the same language that is set on your web server.

2. Modify your connection string
On your SQL connection string, add a parameter of Language= and set it equal to the correct language. For example, Language=British.

Just check your default language settings.

Andy

Keep smiling

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources