error: the converstion of char datatype to data

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)

error: the converstion of char datatype to data

Author

Topic

desikankannan
Posting Yak Master

152 Posts

Posted - 2008-08-13 : 01:26:49

i am sql and vb.net i try to insert invoice date (datetime datatype) if i enter the date in the maskedittestbox like 25/07/2008 and try to save in the

database i throw the below error

the conversion of a char datatype to datetime data type resulted in an out-of-range datetime value the statement has been terminated. source:.Net sqlclient data provider

iam struggling hard for the past two days about the date format , please give some good suggestions

Desikankannan

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-08-13 : 01:59:30

try giving it in mm/dd/yyyy format.. thats the standard sql format

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 02:03:42

nope. standard sql format is yyyy-mm-dd

are you using stored procedure or inline query?

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-08-13 : 02:23:18

yes vishak u r right the standard format is YYYY-MM-DD HH:MM:SS

desikankannan
Posting Yak Master

152 Posts

Posted - 2008-08-13 : 02:44:20

Hi,
iam not understand in my regional setting i have dd/MM/yyyy format
here with iam sending my insert statement please guide iam struggling hard for the past two days date converstion iam using vb.net 2005
error message
Msg 242, Level 16, State 3, Line 1
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.

my insert statement bold letter is the invoice date that
insert into tblinvhd values('5','0','25/07/2008','2007-2008','3','0','10:00','0','11:00','1030','01',' ','courier
','117000','16380','327.6','163.8','133871.4','0','0','4016','137887.4','Sixteen Thousand Three Hundred Eighty Only','One Lakh Thirty Seven Thousand Eight Hundred Eighty Seven And Forty Paise Only','3')

Desikankannan

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-08-13 : 03:00:06

when i tried the same insert statement of yours ts hsowed me the same error, then I've changed the format to mm/dd/yyyy format.Try changing the format it works..
Give the same insert statment like this

insert into tblinvhd values('5','0','07/25/2008','2007-2008','3','0','10:00','0','11:00','1030','01',' ','courier
','117000','16380','327.6','163.8','133871.4','0','0','4016','137887.4','Sixteen Thousand Three Hundred Eighty Only','One Lakh Thirty Seven Thousand Eight Hundred Eighty Seven And Forty Paise Only','3')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 03:03:05

try this and see if it works
set dateformat dmy;
insert into tblinvhd values('5','0','25/07/2008','2007-2008','3','0','10:00','0','11:00','1030','01',' ','courier
','117000','16380','327.6','163.8','133871.4','0','0','4016','137887.4','Sixteen Thousand Three Hundred Eighty Only','One Lakh Thirty Seven Thousand Eight Hundred Eighty Seven And Forty Paise Only','3')

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-13 : 05:19:20

quote:
Originally posted by visakh16

try this and see if it works
set dateformat dmy;
insert into tblinvhd values('5','0','25/07/2008','2007-2008','3','0','10:00','0','11:00','1030','01',' ','courier
','117000','16380','327.6','163.8','133871.4','0','0','4016','137887.4','Sixteen Thousand Three Hundred Eighty Only','One Lakh Thirty Seven Thousand Eight Hundred Eighty Seven And Forty Paise Only','3')

Just remember if you are doing anything else with dates in this query that you have explicitly set the date-format and this applies to the whole query.

I think (if you can control the input data at all) then it is better to stick to the 2 ISO datetime format standards

They are

Short Form (ISO) : YYYYMMDD
Long Form (ISO8601) : YYYY-MM-DDTHH:MM:SS

So both of these should work regardless of your locale / dateformats and give you consistent results wherever you are.

See : http://www.karaszi.com/SQLServer/info_datetime.asp for more info


insert into tblinvhd
values(
	'5'
	,'0'
	,'20080725'
	,'2007-2008'
	,'3'
	,'0'
	,'10:00'
	,'0'
	,'11:00'
	,'1030'
	,'01'
	,' '
	,'courier'
	,'117000'
	,'16380'
	,'327.6'
	,'163.8'
	,'133871.4'
	,'0'
	,'0'
	,'4016'
	,'137887.4'
	,'Sixteen Thousand Three Hundred Eighty Only'
	,'One Lakh Thirty Seven Thousand Eight Hundred Eighty Seven And Forty Paise Only'
	,'3'
	)

AND


insert into tblinvhd
values(
	'5'
	,'0'
	,'2008-07-25T00:00:00'
	,'2007-2008'
	,'3'
	,'0'
	,'10:00'
	,'0'
	,'11:00'
	,'1030'
	,'01'
	,' '
	,'courier'
	,'117000'
	,'16380'
	,'327.6'
	,'163.8'
	,'133871.4'
	,'0'
	,'0'
	,'4016'
	,'137887.4'
	,'Sixteen Thousand Three Hundred Eighty Only'
	,'One Lakh Thirty Seven Thousand Eight Hundred Eighty Seven And Forty Paise Only'
	,'3'
	)

-------------
Charlie

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-14 : 06:20:53

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=108646

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources