Solved

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value

Posted on 2008-10-15
7
968 Views
Last Modified: 2012-05-05
Hello Experts,

I am trying to insert a row in the database but I am getting data conversion error, it was working fine earlier.

Can someone please advise why it should not work?

I have used the following statement to add a record in the table

INSERT INTO LearnItResults (CustomerID, TestID, GiftCodeID, StartTime)
 VALUES(65079,2,'CHOCOXT454545','15/10/2008 14:28:30')

and i have got the following 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.

thanks
kay
ResultID	int	Unchecked

CustomerID	int	Checked

TestID	nvarchar(50)	Checked

GiftCodeID	nvarchar(50)	Checked

CreatedDate	datetime	Checked

StartTime	datetime	Checked

EndTime	varchar(50)	Checked

IsCompleted	bit	Checked

		Unchecked

Open in new window

0
Comment
Question by:learningnet
7 Comments
 

Author Comment

by:learningnet
Comment Utility
same SQL statement in ASP.net

 Dim SqlQuery As StringBuilder = New StringBuilder
                SqlQuery.Append("INSERT INTO LearnItResults (CustomerID, TestID, GiftCodeID, StartTime) VALUES(")
                SqlQuery.Append(CustomerID & ",")
                SqlQuery.Append(TestID & ",")
                SqlQuery.Append("'" & GiftCodeID & "',")
                SqlQuery.Append("'" & System.DateTime.UtcNow & "'")
                SqlQuery.Append(")")

                Dim MyCommand As SqlCommand = New SqlCommand(SqlQuery.ToString(), MySqlConnection)

                MyCommand.Connection.Open()
                MyCommand.ExecuteNonQuery()
                MyCommand.Connection.Close()
0
 
LVL 10

Assisted Solution

by:Tyler Laczko
Tyler Laczko earned 150 total points
Comment Utility
mysql format for datetime is

'YYYY-MM-DD HH:MM:SS'
0
 

Author Comment

by:learningnet
Comment Utility
thanks for your comment

that is a strange it allowed me to run the same statement earlier

can you please advise how would I change the format in the ASP.net script?

thanks

49	1	1	CHOCO2342323	08/10/2008 16:51:16	10/08/2008 15:51:18	NULL	False

Open in new window

0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 1

Assisted Solution

by:ashishkaithi
ashishkaithi earned 150 total points
Comment Utility
hi,
you must write correct format for datetime .
plz. look at the code below u've to write , MM/DD/YYYY HH:MM:SS for datetime
CREATE TABLE expert1(code int PRIMARY KEY,dt DATETIME);

SELECT * FROM expert1

INSERT INTO expert1(code,dt) VALUES(101,'10/15/2008 14:28:30')

Open in new window

0
 

Author Comment

by:learningnet
Comment Utility
ok thanks for your comments

what should I use to get that format?

i am presently using this in my asp.net code --> System.DateTime.UtcNow
0
 
LVL 13

Accepted Solution

by:
TechTiger007 earned 200 total points
Comment Utility
0
 
LVL 1

Expert Comment

by:ashishkaithi
Comment Utility
hi,
use this

System.DateTime.Now.ToString("dd/MM/yyyy hh:mm:ss tt")

for more:
follow this link:
http://blogs.msdn.com/kathykam/archive/2006/09/29/.NET-Format-String-102_3A00_-DateTime-Format-String.aspx
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now