Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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
Medium Priority
?
1,023 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 600 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 1

Assisted Solution

by:ashishkaithi
ashishkaithi earned 600 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 800 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

610 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