The conversion of a char data type to a datetime

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 2005 Forums

Transact-SQL (2005)

The conversion of a char data type to a datetime

Author

Topic

desikankannan
Posting Yak Master

152 Posts

Posted - 2008-07-27 : 14:40:50

Hi,

i try to insert values to the database
insert into tblinvhd values('2','231','23-07-2008 00:00:00','2007-2008','1','23-07-2008 00:00:00','20:00','23-07-2008 00:00:00','22:00','88','01','23-07-2008 00:00:00','courier','55440','7761.6','155.23','77.62','63434.45','0','0','2537.38','65971.83','Seven Thousand Seven Hundred Sixty One And Sixty Paise Only','Sixty Five Thousand Nine Hundred Seventy One And Eighty Three Paise Only')
iam using vb.net 2005 as front end
please guide me very urgent

but i throws the error
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Desikankannan

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-27 : 15:19:58

Change '23-07-2008 00:00:00' to '07-23-2008 00:00:00' then try again.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-27 : 21:43:51

1. Make sure your value matches the columns in table
2. Include the column name in the insert statement


insert into tblinvhd (col1, col2, col3, ...) 
values ('2', '231', . . .)

3. Use Universal Date format YYYYMMDD for all date. Eg. '20080723'

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-28 : 00:20:36

or specify the format in which you're trying to pass the value explicitly using SET DATEFORMAT. in above case it should be,
SET DATEFORMAT dmy

set this before running the insert query

desikankannan
Posting Yak Master

152 Posts

Posted - 2008-07-28 : 05:28:35

Hi,

thanks for your mail, can you explain how to set dateformat in query itself please guide me.

quote:
Originally posted by visakh16

or specify the format in which you're trying to pass the value explicitly using SET DATEFORMAT. in above case it should be,
SET DATEFORMAT dmy

set this before running the insert query

Desikankannan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-28 : 05:42:22

You should always use DAETTIME datatype and express dates in YYYYMMDD HH:MM:SS format to work for all date settings

Madhivanan

Failing to plan is Planning to fail

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-07-28 : 06:10:49

use the datetime datatype instead of varchar
in the table structure.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-28 : 10:34:41

quote:
Originally posted by desikankannan

Hi,

thanks for your mail, can you explain how to set dateformat in query itself please guide me.

quote:
Originally posted by visakh16

or specify the format in which you're trying to pass the value explicitly using SET DATEFORMAT. in above case it should be,
SET DATEFORMAT dmy

set this before running the insert query

Desikankannan

set dateformat dmy

your insert query

i'm assuming your columns are of datetime datatype.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources