Saving dates in SQLServer

Question

I have an outdated application in which the input is a date string, i.e.:

12/06/2009

The input format is always a string and consistent, always dd / mm / yyyy

Currently, the deprecated application simply enters INSERTS in the DateTime field. Obviously, if the settings for the localization culture on the server have changed, we have an error .

Two questions:

One:

What is a safe way to store dates in SQLServer in this situation?

Is there a format that will always be correctly interpreted, regardless of the order of the day or month?

Two:

What settings exactly determine the culture of SQLServer DB, is it an OS setting or a setting for this database, or what?

amuses

Answer 1

The YYYY-MM-DD format is unambiguous, which means that SQL Server will not confuse the month and day when converting a string value to DATETIME . (I have never experienced an implicit conversion problem using this format using a four-digit year.)

The “safest” (and most convenient) way to store date values ​​in SQL Server is to use the DATETIME data type.

Use the CONVERT function to explicitly specify input and output formats when converting between DATETIME and strings.

SQL Server 2005 documentation for CONVERT style values:

http://msdn.microsoft.com/en-us/library/ms187928(SQL.90).aspx

To convert a string representation to a DATETIME data type:

 select CONVERT(datetime, '2009-06-03', 20) 

The first argument is the data type to convert, the second argument is the converted expression, the third argument is the style.

(style 20 - Canonical ODBC format = 'YYYY-MM-DD HH:MI:SS' (24-hour mode)

---

[Followup]

To convert a DATETIME expression (e.g. getdate () to VARCHAR in the format 'YYYY-MM-DD' :

 select CONVERT(varchar(10), getdate(), 20) 

Note that specifying varchar (10) gives you only the first 10 characters of the etnire format 'YYYY-MM-DD HH:MM:SS' .

[/ Followup]

---

As for the default formats, this will be a study. We avoid problems caused by default formats by defining formats.

Answer 2

See SET DATEFORMAT . The SQL culture is set by SET LANGUAGE at the session level. SQL Server has its own date format settings, regardless of the hosting OS. This is due to several reasons: compliance with ANSI to prevent changes to the OS in applications using the database hosted on this node, and last but not least, compatibility, SQL long precedes the OS is currently working.

Answer 3

I would recommend storing all dates in UTC when they are placed in the database. It will be as consistent.

Saving such dates seems to work well ...

 YYYY-MM-DD 

Answer 4

Keep in mind that DATA is not his PRESENTATION. In this case, DATA is DATE or DATETIME, regardless of how you display them.
Regarding inserting / updating / comparing date and time values, I quote BOL:

When specifying dates in comparison or for entering into INSERT or UPDATE, the operators used are interpreted the same way for all configuration languages: ADO, OLE DB and ODBC applications should use ODBC timestamp, article date and time:
{ts 'yyyy-mm-dd hh: mm: ss [.fff]'}, for example: {ts '1998-09-24 10:02:20'}
{d 'yyyy-mm-dd'}, for example: {d '1998-09-24'}
{t 'hh: mm: ss'}, for example: {t '10: 02: 20'}

I can assure you that if you use these formats, they will always work regardless of the language of your server.

Answer 5

I am a little conservative in these matters, but I prefer to use separate Year / Month / Day fields in the table, rather than the Date field, which uses a data type specific to the DBMS. It certainly takes up more space, but the lack of ambiguity and increased portability is worth it to me.

The price you pay is that you do not get free date and time arithmetic and sorting, but it’s easy enough to make yourself or a little more complicated ORDER BY clause.

Answer 6

I agree with the advice from spencer7593, but please keep in mind that using casts or transforms without formatting may produce unexpected results. This T-SQL query returns 12, not 1.

 set language British select month(CAST('2016-01-12' AS datetime))