Geek Answers Handbook

Best way to parse DateTime on SQL server

I was wondering what is the best way to parse a DateTime object into your SQL server.

Where do you create SQL in code.

I always used something like DateTime.Now.TolongDateString() and had good results, except today, when I got the error, and it made me think.

 System.Data.SqlClient.SqlException: Conversion failed when converting datetime from character string

So, what are all the thoughts and recommendations for a method that will work on any SQL server no matter what is installed there.

Maybe something like DateTime.Now.ToString("yyyy/MM/dd")

+6
sql datetime sql-server tsql
source share
7 answers

there are only 2 safe formats

ISO and ISO8601

ISO = yymmdd

ISO8601 = yyyy-mm-dd Thh: mm: ss: mmm (no spaces) (note T)

See also here: Installing a Standard DateFormat for SQL Server

+14
source share

Why not parameterize the query and pass the DateTime value as an input parameter to SQL DateTime?

eg INSERT INTO SomeTable (Blah, MyDateTime) VALUES (1, @MyDateTime)

Then you can be really sure. Even if you are creating SQL, should you be able to handle this specifically?

+10
source share

I found this specific format:

 DateTime.Now.ToString("yyyy-MM-ddTHH:mm:ss.fff")

Pay attention to the case and the absence of any spaces. This is a universal way according to Robyn Page .

+10
source share

You should really use parameterized queries for this and pass your DateTime object as an SQL DateTime parameter.

If you parse a DateTime into a String , you will have to deal with localization parameters on both the application server and the database server. This can lead to some unpleasant surprises, for example, to relate to the Date, as it was in the US format on the one hand and, for example, the UK on the other. Line 9/12/2000 can be either September 12th or December 9th. Therefore, when exchanging between an application and a database, save the data in a DateTime object / type.

The only time you parse a DateTime into a String is to display information (GUI). But then you have to make sure that you use the correct localization setting during parsing in order to display it in the format that the user expects.

The same principle applies to other data types, such as float . The string representation of this varies by locale, and I suppose you don't parse the float to String when passing it to the database, so why do it with DateTime

+5
source share

this one will never fail: DateTime.Now.ToString ("yyyyMMdd HH: mm: ss.fff")

+3
source share

Beware of DateTime.Min dates, as SQL Server uses 1700 at the very beginning. I would use the ISO date and time format: DateTime.ToString ("s"), but I have not tried this on non-Western settings.

eg.

 DateTime.Now.ToString("c")

is an

 insert into testtable values ('2009-01-22T15:08:13')
+3
source share

Formatting using DateTime.ToString ("yyyy-MM-dd HH: mm: ss: fff") will correspond to the MS SQL Server date and time format. (I think SQL Server is smart enough to recognize slightly different formats, for example, with a slash, but I personally used this option successfully.)

EDIT: As the commentator noted, this is likely to be destroyed in some locales.

+1
source share

More articles:


All Articles