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
1,005 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

733 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