-1

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 ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
samer
  • 25
  • 1
  • 2
  • 6
  • 4
    Do *not* insert values in the sql statement string in code! *Do* use [SqlParameter](http://msdn.microsoft.com/library/system.data.sqlclient.sqlparameter.aspx). – Corak Jun 13 '13 at 08:30
  • http://stackoverflow.com/questions/1032495/insert-datetime-value-in-sql-database-with-c-sharp – Zaki Jun 13 '13 at 08:32
  • 3
    `cmd.CommandText = "insert into date(startdate) values (@startDate)"; cmd.Parameters.AddWithValue("startDate", date_dt);` - ***job done*** – Marc Gravell Jun 13 '13 at 08:42

5 Answers5

1

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

Sklivvz
  • 30,601
  • 24
  • 116
  • 172
  • 1
    Didn't downvote but I think this doesn't answer the question. Even after your edit. – Soner Gönül Jun 13 '13 at 08: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 '13 at 08: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 '13 at 08:40
1

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.

EventHorizon
  • 2,916
  • 22
  • 30
  • 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 '13 at 08: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. – EventHorizon Jun 13 '13 at 08:44
0

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/

DSharper
  • 3,177
  • 9
  • 29
  • 47
  • 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 '13 at 08:47
0
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...
Umesh Sehta
  • 10,555
  • 5
  • 39
  • 68
-1

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

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

I hope it will help you. :)

Hitesh
  • 3,508
  • 1
  • 18
  • 24