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
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,000 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
insert value of checklistbox checked 4 32
C# LINQ ForEach() question 6 53
Combining Queries 7 27
SQL querys that gives me from one table into another. 2 23
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
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…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

790 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