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
994 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
ID: 22721524
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
ID: 22721569
mysql format for datetime is

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

Author Comment

by:learningnet
ID: 22721623
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 1

Assisted Solution

by:ashishkaithi
ashishkaithi earned 150 total points
ID: 22721728
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
ID: 22721853
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
ID: 22722411
0
 
LVL 1

Expert Comment

by:ashishkaithi
ID: 22722692
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

770 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