Best IT Recipes

SQL date and time format using C #

I am trying to save the current date time format from C # and convert it to SQL Server date format, for example yyyy-MM-dd HH:mm:ss , so I can use it for my Update request.

This was my first code:

 DateTime myDateTime = DateTime.Now; string sqlFormattedDate = myDateTime.Date.ToString("yyyy-MM-dd HH:mm:ss");

The exit on the date is approved, but the time is always "12:00:00", so I changed my code to the following:

 string sqlFormattedDate = myDateTime.Date.ToString("yyyy-MM-dd") + " " + myDateTime.TimeOfDay.ToString("HH:mm:ss");

This gave me a compilation error "FormatException was unhandled" and suggested that I analyze it. So I tried to do this with my code according to my research here in staackoverflow:

 string sqlFormattedDate = myDateTime.Date.ToString("yyyy-MM-dd") + " " + myDateTime.Parse.TimeOfDay.ToString("HH:mm:ss");

or

 string sqlFormattedDate = myDateTime.Date.ToString("yyyy-MM-dd") + " " + myDateTime.tryParse.TimeOfDay.ToString("HH:mm:ss");

but this gives me a method that is not valid for the given context. I tried to find solutions to my problem and am currently stuck for two hours. I'm still a little new to C #, can you help?

+55
c # sql datetime
Jul 02 '13 at 5:51 on
source share
11 answers

try it below

 DateTime myDateTime = DateTime.Now; string sqlFormattedDate = myDateTime.ToString("yyyy-MM-dd HH:mm:ss.fff");
+112
Jul 02 '13 at 5:55 on
source share

Using the standard datetime "s" format will also ensure compatibility with internationalization (MM / dd versus dd / MM):

 myDateTime.ToString("s"); => 2013-12-31T00:00:00

Full parameters: (code: sample result)

 d: 6/15/2008 D: Sunday, June 15, 2008 f: Sunday, June 15, 2008 9:15 PM F: Sunday, June 15, 2008 9:15:07 PM g: 6/15/2008 9:15 PM G: 6/15/2008 9:15:07 PM m: June 15 o: 2008-06-15T21:15:07.0000000 R: Sun, 15 Jun 2008 21:15:07 GMT s: 2008-06-15T21:15:07 t: 9:15 PM T: 9:15:07 PM u: 2008-06-15 21:15:07Z U: Monday, June 16, 2008 4:15:07 AM y: June, 2008 'h:mm:ss.ff t': 9:15:07.00 P 'd MMM yyyy': 15 Jun 2008 'HH:mm:ss.f': 21:15:07.0 'dd MMM HH:mm:ss': 15 Jun 21:15:07 '\Mon\t\h\: M': Month: 6 'HH:mm:ss.ffffzzz': 21:15:07.0000-07:00

Supported in .NET Framework: 4.6, 4.5, 4, 3.5, 3.0, 2.0, 1.1, 1.0
Reference: DateTime.ToString Method

+20
Jan 02 '14 at 4:25
source share

The correct answer has already given "usage parameters". Formatting the date and passing it as a string to SQL-Server can lead to errors, because it depends on the settings of how the date is interpreted on the server side. In Europe, we write "December 1, 2012" to indicate December 1, 2012, while in other countries this can be considered as January 12.

When issuing statements directly to SSMS, I use the yyyymmdd format, which looks pretty general. I did not encounter any problems on the various installations that I have worked on so far.

There is another rarely used format that is a bit strange, but works for all versions:

 select { d '2013-10-01' }

will return the first of October 2013.

 select { ts '2013-10-01 13:45:01' }

will return on October 1, 1:45:01

I highly recommend using parameters and never formatting your own SQL code by inserting home-made formatted report fragments together. This entry is for SQL injection and weird errors (formatting the float value is another potential problem)

+5
Jul 02 '13 at 6:34
source share

Your first code will work by doing this

 DateTime myDateTime = DateTime.Now; string sqlFormattedDate = myDateTime.ToString("yyyy-MM-dd HH:mm:ss"); //Remove myDateTime.Date part
+3
Jul 02 '13 at 5:56 on
source share

Your problem is the Date property, which truncates the DateTime by date only. You can put the transformation as follows:

 DateTime myDateTime = DateTime.Now; string sqlFormattedDate = myDateTime.ToString("yyyy-MM-dd HH:mm:ss"); // <- No Date.ToString()!
+3
Jul 02 '13 at
source share

The answer I was looking for was:

 DateTime myDateTime = DateTime.Now; string sqlFormattedDate = myDateTime.ToString("yyyy-MM-dd HH:mm:ss");

I also found out that you can do this as follows:

 DateTime myDateTime = DateTime.Now; string sqlFormattedDate = myDateTime.ToString(myCountryDateFormat);

where myCountryDateFormat can be modified to meet the changes depending on the requirements.

Note that the tag “This question may already have an answer here:” did not actually answer this question because, as you can see, it used “.Date” instead of skipping it. This is pretty confusing for new .NET programmers.

+1
Jul 02 '13 at 22:47
source share

Why not skip a line at all:

 SqlDateTime myDateTime = DateTime.Now;
+1
Jul 02 '13 at 23:12
source share

only you put "T" + DateTime.Now.ToLongTimeString () + '2015-02-23'

0
Jun 29 '15 at 1:38
source share

If you want to update the table with this date, you can use your sql string, as in this example:

 int fieldId; DateTime myDateTime = DateTime.Now string sql = string.Format(@"UPDATE TableName SET DateFieldName='{0}' WHERE FieldID={1}, myDateTime.ToString("yyyy-MM-dd HH:mm:ss"), fieldId.ToString());
0
Jul 03 '15 at 16:48
source share

Your problem is the Date property, which truncates DateTime only by date. You can put the transformation as follows:

 DateTime myDateTime = DateTime.Now; string sqlFormattedDate = myDateTime.ToString("yyyy-MM-dd HH:mm:ss");
0
Aug 04 '15 at 5:38
source share

if you want to keep the current date in a table so you can use

GETDATE ();

or pass this function as a parameter

eg. 'update tblname set curdate = GETDATE (), where colname = 123'

0
Nov 30 '15 at 13:22
source share


More articles:


All Articles