Explore our sites

Stack Exchange
tour help careers 2.0
Take the tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote -1 down vote favorite

I have string date="20130613070000+1000" I convert to datatime by using:

date_dt = DateTime.ParseExact(date, "yyyyMMddHHmmsszzz", null, DateTimeStyles.None);

results its date_dt=13/06/2013 12:00:00 AM

I insert into database SQL Server using 

insert into date (startdate) VALUES ('13/06/2013 12:00:00 PM')

I have a problem - I get an error:

Msg 242, Level 16, State 3, Line 6
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

What's the solution for dt_date convert with fomat MM/dd/yyy ?

share|improve this question
4  
Do not insert values in the sql statement string in code! Do use SqlParameter. –  Corak Jun 13 at 8:30
 
stackoverflow.com/questions/1032495/… –  Zaki Jun 13 at 8:32
3  
cmd.CommandText = "insert into date(startdate) values (@startDate)"; cmd.Parameters.AddWithValue("startDate", date_dt); - job done –  Marc Gravell♦ Jun 13 at 8:42
add comment

5 Answers

up vote 1 down vote

How are you inserting in the database? From C# code you should always use parameters for passing values. The framework does the conversion for you!

More info: http://msdn.microsoft.com/en-us/library/ms254953.aspx

http://msdn.microsoft.com/en-us/library/4f844fc7.aspx

share|improve this answer
1  
Didn't downvote but I think this doesn't answer the question. Even after your edit. –  Soner Gönül Jun 13 at 8:31
3  
@SonerGönül on the contrary, I think it is the answer to this question. If you ever find yourself having to wonder how to parse or format dates (or any other type, for that matter), then it means you are doing it wrong. The correct thing to do here is to express that as a parameter, rather than as a literal –  Marc Gravell♦ Jun 13 at 8:39
1  
@SonerGönül the question is (also) "how to insert datetime by c#" there's no doubt that the standard way is via SqlParameters. –  Sklivvz♦ Jun 13 at 8:40
add comment
up vote 1 down vote

You can use the ISO 8601 format when inserting the date as a string. This should work across all cultures:

date_dt.ToString("s")

Example output:

2013-06-13T10:29:57

Alternatively, you can use a SqlCommand with parameters which will do the conversion for you.

share|improve this answer
 
not this i need to insert ino sql by datetime type but i need to equals betwen datetime format in sql and datetime format in asp.net or c# –  samer Jun 13 at 8:38
 
I'm not sure if I understand what you mean. The output from SQL server is a DateTime instance. If your date value on the server also is a DateTime instance comparing them should be easy? You might have to convert to/from UTC if you support multiple timezones. –  Dag Jun 13 at 8:44
add comment
up vote 0 down vote

you can use following convert function to convert your date string to SQL date time format

DECLARE @MyDate DATETIME 
SET @MyDate = CONVERT(DATETIME, '13/06/2013 12:00:00 PM', 103) 
INSERT INTO date (startdate) VALUES (@MyDate)

Very useful link to bookmark is found here related to this. http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/

share|improve this answer
1  
Since the value is coming from the calling code (and isn't a fixed value in some static sql), IMO it would still be far better to parameterize the value in the first place, rather than concatenate it into a literal. Parameters are strongly-typed - no need for a potentially ambiguous format/parse step. It allows better query plan re-use, too. –  Marc Gravell♦ Jun 13 at 8:47
add comment
up vote 0 down vote 
You can insert into database SQL Server using

insert into date (startdate) VALUES ('06/13/2013 12:00:00 PM')

the format for datetime is mm/dd/yy...
share|improve this answer
add comment
up vote -1 down vote

If you putting into the sql query, then you can use below:

select CONVERT(VARCHAR(10),getdate(),101)

I hope it will help you. :)

share|improve this answer
 
There's a mistake: getdate? –  Sklivvz♦ Jun 13 at 8:37
 
What is that..?? –  Hitesh Mistry Jun 13 at 8:38
 
getdate() returns the current date. He's trying to insert other data. –  Sklivvz♦ Jun 13 at 8:39
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.