Geek Answers Handbook

Safe DateTime in T-SQL INSERT statement

Recently, there has been a problem using DateTime in a T-SQL INSERT INTO statement. Work fine on one machine, but you cannot work on another, and I assume this is due to locale settings.

So, if I have a DateTime variable, then what is the safe way to use it in the SqlStatement line, which will always work regardless of the local system settings?

thanks

+4
source share
3 answers

Use a parameterized INSERT query.

Most likely, your code collects a string of SQL commands. It also makes your code vulnerable to SQL Injection .

+9
source

You should use a parameterized query, as Adrian suggested.

Another possibility is to use the ISO 8601 string representation as described here , which is independent of the locale settings.

It will look like this:

 20110921 15:20:00
0
source

Or you use parameterized commands / stored procedures, where you create a parameter of type DateTime in stored ones and assign it from the .net code when calling the stored one (so that .NET and SQL will know that they work with date-time and will never confuse / swap day and month), or you include a specific command on top of your insert commands, and then format all data lines with this template, for example:

 SET DATEFORMAT dmy;

SET DATEFORMAT (Transact-SQL)

0
source

More articles:


All Articles